sql Query with wrong output

[reidarT]

I have a Booking query with an arrivaldate and a departuredate.

ArrivalDate 18.01.06
DepartureDate 08.09.06

Records from table tblHot

ID HotID FromDate ToDate Price
1 1 01.01.06 08.01.06 2
2 1 09.01.06 20.01.06 3
3 1 21.01.06 27.03.06 5
4 1 28.03.06 30.04.06 3
5 1 01.05.06 31.12.06 2
6 2 02.01.06 31.12.06 7


Dim sqlDates as string

sqlDates = _
"SELECT HotID, FromDate, ToDate, Price " & _
"FROM tblHot " & _
"WHERE tblHot.HotID=1 AND tblHot.FromDate Between #1/18/2006# And
#9/8/2006# " & _
"OR tblHot.HotID=1 AND tblHot.ToDate Between #1/18/2006# And #9/8/2006#
" & _
"ORDER BY tblHot.FromDate"

When I run this query I get 0 records
as result.
The language is Norwegian.

I want a list with 4 records listed, but I get 0

The dates #1/18/2006# And #9/8/2006# are actually from a form with 2 fields

reidarT

 

[Allen Browne]

I think you are saying that instead of the literal dates in your query, the
Criteria row contains something like:
Between [Forms]![Form1]![StartDate] And [Forms]![Form1]![EndDate]

If so:

1. Declare the parameters.
In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog:
[Forms]![Form1]1[StartDate] Date/Time
[Forms]![Form1]1[EndDate] Date/Time

2. If StartDate and EndDate are unbound text boxes, set their Format
property to Short Date or similar, so Access understands them as dates.

3. Enter the dates in the form they way you normally do in your interface.
From your example, that seems to be dd.mm.yyyy.

More info:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[John Smith]

Almost always when mixing AND and OR you need to use brackets to control which
tests are ANDed and which ORed. I think that you want:

sqlDates = "SELECT HotID, FromDate, ToDate, Price FROM tblHot" & _
" WHERE HotID=1 AND (FromDate Between #1/18/2006# And #9/8/2006#" & _
" OR ToDate Between #1/18/2006# And #9/8/2006#)" & _
" ORDER BY FromDate"

HTH
John