Comparing dates in Access

[cusimar9]

I've spent far too much time on this now and its driving me crazy :mad:

I am using an Access database and ASP

I have 2 fields in one table, one that holds a date and one that holds a time.

I have 2 strings, one with a date, and one with a time, which I need to compare to the fields in the table with a query.

For the moment I'm completely stuck on the date, haven't even tried the time yet!

This doesn't work:

"SELECT Count(*) AS TotalQty FROM MyTable WHERE Datefield = 14/09/2005"

I've tried just about everything with no luck. Can anyone suggest something? I know it stores the date as the integer and time as the real part, but I still can't seem to get it to compare only the date.

I've set the locale to be 2057 so its in dd/mm/yyyy, i've tried switching all the values round etc with no luck.

Someone must be able to point me in the right direction?

[Bullschmidt]

I'm thinking you might have to convert that 14/09/2005 to be more like 09/14/2005 just so the underlying Access database understands it better.

And here are some other date related things that I've done that may give you some ideas.

Example setting a date range based on posted fields:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

Example setting a date range to be the current and previous months:

strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"

To show invoices whose dates are on Fridays:

strSQL = "SELECT InvID, InvDt FROM tblInv WHERE (Weekday(InvDt)=6)"

Here's a SQL statement I just tested on a database of mine to show what week of the year each invoice is dated:

strSQL = "SELECT DatePart('WW',[InvDt]) AS TheWeek FROM tblInv"

And to make a variable be in the format of mm/dd/yyyy (and the final line of code can be modifed for other date formats), perhaps try something like the following which you might even make into a function that you could put on a page that gets included in your main pages:

varFld = CDate(MyVariable)

intMonth = Month(varFld)
intDay = Day(varFld)
intYr = Year(varFld)

If intMonth < 10 Then
strMonth = "0" & CStr(intMonth)
Else
strMonth = CStr(intMonth)
End If

If intDay < 10 Then
strDay = "0" & CStr(intDay)
Else
strDay = CStr(intDay)
End If

strYr = Right(CStr(intYr), 4) ' And change the 4 to 2 for 2 year dates.

varFld = CStr(strMonth & "/" & strDay & "/" & strYr)

[cusimar9]

Thanks, I'll give that a try :)

[cusimar9]

I CANNOT BELIEVE IT!

The only thing I was missing was the sodding hash # signs :mad: :rolleyes:

Thanks so much for your help, its all working now :D