date query-desperate

[bjackson]

i have tried every thing to get this query to work.


there must be a simple answer.If i use # to enclose the dates
the query returns no record.if i dont use the # the query returns all records regardless
of the date

Set TopQdf = Dbs.CreateQueryDef("", "SELECT TOP 14 AvailableDates.DatesAvailable, AvailableDates.id" _
& " " & "FROM AvailableDates" _
& " " & "WHERE (((AvailableDates.DatesAvailable) >= #" & [Forms]![datequery]![QueryDate] & "#" & "))" _
& " " & "ORDER BY AvailableDates.id;")


please can any one assist

thanks in advance

 

[raskew]

I don't think it's your code. To understand the problem I built a virtually identical sub in Northwind and it works as advertised.

Since you are getting records returned when you omit the "#", I'd suspect that field AvailableDates.DatesAvailable may be formatted as Text rather than DateTime.

 

[Rich]

And it might be that dates are not in the US format, so try
Set TopQdf = Dbs.CreateQueryDef("", "SELECT TOP 14 AvailableDates.DatesAvailable, AvailableDates.id" _
& " " & "FROM AvailableDates" _
& " " & "WHERE (((AvailableDates.DatesAvailable) >= #" & Format([Forms]![datequery]![QueryDate],mm dd yyyy") & "#" & "))" _
& " " & "ORDER BY AvailableDates.id;")

 

[bjackson]

Thanks Rich your right

2 days of head banging can sure work up a thirst

thaks for the help

regards bjackson