p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Trouble with date formats (http://p2p.wrox.com/showthread.php?t=26385)

DrewMills March 3rd, 2005 08:07 PM

Trouble with date formats
Here's one people in the States might not have encountered, but it's a curly one for me!

I have a two combo boxes in a form where the user selects a start and end date for a record search. My code retrieves these values, creates an SQL string and changes the underlying QueryDef. The dates are of course dd/mm/yyyy format (Australia).

    If chkDate Then
        dateStart = cmbStartDate.Value
        dateEnd = cmbEndDate.Value
        strSQL = strSQL & " tblLog.[Log Date] Between #" & dateStart & "# And #" & dateEnd & "#"
    End If

My problem is this: when the SQL string is sent to the QueryDef, Access attempts to change the date format to mm/dd/yyyy. This is fine for unambiguous dates, such as 18/2/2005, but where the day is less than 12, it invariably ends up reversed, even if I convert all of my dates to mm/dd/yyyy first. Does anyone have any suggestions?


mmcdonal March 4th, 2005 10:13 AM

I am not sure why you need to force the ## date issue in the SQL string. Try this for grins:

strSQL = strSQL & " tblLog.[Log Date] Between " & "'" & dateStart & "'" & " And " & "'" & dateEnd & "'"

Access should just take the value without forcing the date issue.



DrewMills March 6th, 2005 07:01 PM

Hmm, well I just end up with a run-time error saying I cancelled the previous operation. This relates to a "data type mismatch" in the criteria of the query. Doesn't seem to like the 'dd/mm/yyyy' format. I tried enabling ANSI92 syntax, but it didn't help. looks like I might just have to rearrange the dates using string handlers:-(

elansolutionsltd March 7th, 2005 06:22 PM

Good Ole US Dates. Don't you just love them!

 Try using
strSQL = strSQL & " tblLog.[Log Date] Between #" & Format(dateStart,"mm/dd/yy") & "# And #" & Format(dateEnd,"mm/dd/yy") & "#"

Unless you are using an SQL Server, this usually works, but only if dateStart and dateEnd are Dim'd as dates.

Good luck


All times are GMT -4. The time now is 06:04 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.