syntax problem in Access module using Date

[rdjohnso]

I have a function with the following SQL string that is not executing properly in the module... I can pull back the proper results with a query but when i try to properly translate it in my SQL string in the module...
I keep getting various errors....

Can someone look at this and tell me how it should be syntacticly?

Thanks

Ron

Set cnn = CurrentProject.Connection
Set rs = New ADODB.RecordSet

strSQL = "SELECT COUNT(tbl_Holidays.HoliName) AS cntHoliday FROM tbl_Holidays " _
& " WHERE (tbl_Holidays.Holidate) Between " _
& " #iStartDate# " AND "#iEndDate#" & ""


rs.Open strSQL, cnn, adopenStatic
totHolidays = cntHoliday
lngRecs = rs.Fields(0)
intWkDays = intWkDays = lngRecs

 

[PHV]

I guess that iStartDate and iEndDate are variables defined and populated elsewhere:
strSQL = "SELECT COUNT(HoliName) AS cntHoliday FROM tbl_Holidays " _
& " WHERE Holidate Between #" _
& Format(iStartDate, "yyyy-mm-dd") & "# And #" _
& Format(iEndDate, "yyyy-mm-dd") & "#"

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

 

[rdjohnso]

dude, you rock.. that worked.... super thanks..

Ron