Access Query VS ASP query

[vangundy]

When I use my query in the Access database the results are correct. When I run the query in my asp code the results are not correct.

varDate1 = #03/04/05#
varDate2 = #03/04/05#

Here is the query in the Access database:

SELECT [Table1].[SerialNo], [Table1].[date1], [Table1].[date2], [Table1].[Date_Out], [Table1].[Date_Return]
FROM Table1
WHERE ((([Table1].[SerialNo])=[SerialNo]) And (([Table1].[date1]) Between [date_out] And [date_return]))
Or ((([Table1].[date2]) Between [date_out] And [date_return]))
Or ((([Table1].[Date_Out]) Between [date1] And [date2]))
Or ((([Table1].[Date_Return]) Between [date1] And [date2]));

Here is the query I am using in my asp code:

sqltext = "SELECT [Table1].[SerialNo], [Table1].[Date_Out], [Table1].[Date_Return] FROM Table1
WHERE ((([Table1].[SerialNo])='" & varSerialNo & "') And ((#" & varDate1 & "#) Between [date_out] And [date_return]))
Or (((#" & varDate2 & "#) Between [date_out] And [date_return]))
Or ((([Table1].[Date_Out]) Between #" & varDate1 & "# And #" & varDate2 & "#))
Or ((([Table1].[Date_Return]) Between #" & varDate1 & "# And #" & varDate2 & "#));"

 

[cmmrfrds]

You are probably using an ADO connection in the ASP page and ADO uses standard SQL conventions, such as, single quote for literals and % as wildcard. Try putting single quotes around the dates.

 

[PHV]

And use non ambigous date: yyyy-mm-dd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244