SQL Between Dates Problem

[andybeanland]

Hello, I'm having a problem with a simple query.

In the Query Builder in Access I have created a query to count the number of records between certain dates.

SELECT Count([tbl_PacksSent].[DayCare]) AS NoOfDayCarePacks
FROM tbl_PacksSent
WHERE ((([tbl_PacksSent].[DayCare])=-1) And (([tbl_PacksSent].[DateSent]) Between [Forms]![frm_DateSelectorNoOfPacks].[DateTo] And [Forms]![frm_DateSelectorNoOfPacks].[DateFrom]))

When run this query, inputing the dates 01/01/05 and 01/02/05 when prompted it returns the correct result - 6 records.

However, I really want to open a recordset so I can pass this value to control in another form, so I've created the following code which is executed on the Event OnClick of a button.

DateTo = [Forms]!frm_DateSelectorNoOfPacks.DateTo
DateFrom = [Forms]!frm_DateSelectorNoOfPacks.DateFrom

MsgBox DateTo
MsgBox DateFrom

SQLDayCare = "SELECT Count([tbl_PacksSent].[DayCare]) AS NoOfDayCarePacks FROM tbl_PacksSent WHERE ((([tbl_PacksSent].[DayCare])=-1) And (([tbl_PacksSent].[DateSent]) Between " & DateTo & " And " & DateFrom & "))"

'Defining the connection
Set conDatabase = CurrentProject.Connection
    
'Opening the recordset
Set rs = New Recordset
rs.Open SQLDayCare, conDatabase, 3, 3
MsgBox "Recordset open"
MsgBox rs("NoOfDayCarePacks")

The recordset opens but the MsgBox rs("NoOfDayCarePacks") returns 0. I have done a Debug.Print of the SQL code and it gives the following:

SELECT Count([tbl_PacksSent].[DayCare]) AS NoOfDayCarePacks FROM tbl_PacksSent WHERE ((([tbl_PacksSent].[DayCare])=-1) And (([tbl_PacksSent].[DateSent]) Between 01/01/2005 And 01/02/2005))

Can anyone spot my stupid mistake? or suggest a better way to do this?

Thanks for you help,


Andy

 

[PHV]

Try this:
SQLDayCare = "SELECT Count(DayCare) AS NoOfDayCarePacks FROM tbl_PacksSent WHERE DayCare=-1 And DateSent Between #" & DateTo & "# And #" & DateFrom & "#"


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

 

[andybeanland]

That works, thanks! I knew it was something simple!

 

[andybeanland]

Sorry, that didn't work - I got a bit excited and typed the wrong dates. Unfortunately, I know there are 6 records and it's still returning 0 results. Any ideas?

 

[PHV]

With the above sql, DateTo must be less or equal than DateFrom and both dates must show as mm/dd/yyyy

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

 

[andybeanland]

That seems to be the problem - the form is taking the date in the format dd/mm/yyyy. I tried putting them in as mm/dd/yyyy and it works ok. Whats the best way to get round this problem?

Thanks

 

[PHV]

SQLDayCare = "SELECT Count(DayCare) AS NoOfDayCarePacks FROM tbl_PacksSent WHERE DayCare=-1 And DateSent Between #" & Format(DateTo, "yyyy-mm-dd") & "# And #" & Format(DateFrom, "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

 

[andybeanland]

Cheers PH, your pretty good at this SQL stuff eh! ;-) Really appreciate the help, thanks.

Andy