search on dates

[weezles]

hiya

Dates are driving me insane!
I want to search between two dates that the user enters on a form. I then want to save the results from this query into a table, including one of the dates entered.
My code is as follows:

Dim dtWkEnd as date
dtWkEnd = me.txtTo

"INSERT INTO tblHighRpt (TotSeen, WkEnd) 
SELECT Count(tVisits.ClientID) AS TotSeen, " & dtWkEnd & " "
"FROM tVisits " & _
"WHERE (tVisits.Date) Between #" & (Me.txtFrom) & "# And #" & (Me.txtTo) & "#));"

This gives the correct dates in the query created, however the date it enters into the table is 30/12/1899

Can anyone help?

Lou

 

[SJMcAbney]

INSERT INTO tblHighRpt (TotSeen, WkEnd)
SELECT Count(tVisits.ClientID) AS TotSeen, #" & Format(dtWkEnd, "mm/dd/yyyy") & "# "
"FROM tVisits " & _
"WHERE (tVisits.Date) Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & "# And #" & Format(Me.txtTo, "mm/dd/yyyy") & "#));"

I've formaatted the dates as this bypasses any regional settings since Microsoft products have a tendency to default to US date settings.

Stewart J. McAbney | Talk History

 

[weezles]

Thanks Stewart, that works a treat!

I had used the formatting, though wasn't sure how to Format dtWkEnd.

Is this the way you should always format dates regardless of settings? ie mm/dd/yyyy. I'm having problems with a couple of databases switching round the dates when i format them. In the query builder dates are in a different format that in the SQL, which one is it that the query is actually using?

I get so confused :-S

Lou

 

[SJMcAbney]

As a precaution, and caution is an important word, it's best to play safe and format the date to that American format.

No idea what Americans do though.

Stewart J. McAbney | Talk History

 

[PHV]

The safest way to avoid any regional setting issue is to use an ANSI-like format:
#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