When is a date not a date? Seeking ultimate regional date settings solution...

[ML!]

This problem has plagued me for years and search as I might, I haven't come up with the perfect solution. I'm in Canada and work for clients in the US, the UK etc. While I'm not a programmer, sometimes, I use Access to help them solve relatively simple business problems. Sometimes these are multi-user applications.

I provide the foregoing context because I cannot change a user's regional date settings as a means to resolve my problem.

I have a report that is used in the US (M/D/YY) and Canada (D/M/YY). The criteria dates are set by the user in unbound text boxes on a criteria form. When the user provides the start and end dates, I use a filter to filter the report.

The format for the text box is "mmm dd yyyy" to avoid the regional settings. The user can type it in or use the date picker calendar provided in Access 2007. Either way, when he/she chooses Oct 01 2010, the report still filters using Jan 10 2010 with Canadian regional settings.

I tried using the following code to standardize the date values.

On the cmdOK_Click event of the criteria form, the dates are captured thusly:

strStart = Format(txtStartDate, "mmm dd yyyy")
dteStart = CDate(strStart)
 
strEnd = Format(txtEndDate, "mmm dd yyyy")
dteEnd = CDate(strEnd)

I thought if I took exactly what was in the text box then let Access convert the string using CDate, it would get it right. Then on the Report_Open event, the filter is applied as follows...

Me.Filter = "ShiftDate Between #" & dteStart & "# and #" & dteEnd & "#"
Me.FilterOn = True

Didn't work.

I also tried populating a string variable then taking parts of the date value returned by the user's machine using the Left, Right and Mid functions. Then I translated them using the Month, Day and Year functions for the different parts.

strStartDate = Left(txtStartDate, 2) & "/" & Mid(txtStartDate, 3,2) & "/" & Right(txtStartDate,4) 
 
dteStart = Month(strStartDate) & "/" & Day(strStartDate) & "/" & Year(strStartDate)

Clunky but I thought if I took what the user's machine returned from the text box there would not be any question what the date parts were when the date variable dteStart translated for the filter. That didn't work when one of the user settings displayed the month or day with only one character (1/10/2010 or 10/1/2010). I could use Select Case to evaluate the permutations of what an individual's workstation might return but that seemed silly. It also doesn't solve whether the text box value is 1/10 or 10/1.

What is the ultimate solution to ensure a user in Canada and a user in the US get the same results when they run the report? Is the DateSerial function the right way to go? If so, how would I use it?

 

[boblarson]

The thing is - you don't need to worry about how to store the dates. Let Access use the regional settings all it wants. But where you are using dates in the criteria you will need to use specific formatting to the U.S. Date format to pull it off.

So something like this:

Between "#" & Format([StartDate], "mm\/dd\/yyyy") & "#" And  "#" & Format([EndDate], "mm\/dd\/yyyy") & "#"

 

[boblarson]

See this page for more about that.

 

[ghudson]

Interesting. MsgBox Format(Now, "mm\/dd\/yyyy") Thanks Bob!

 

[ML!]

Bob,

I am EVER so grateful! You have literally saved me hours and hours of trying to figure out a better solution virtually every time I encounter it.

Don't you just love Allen Browne?! I can't believe I didn't consider looking there for a resolution. The simple nugget that JET SQL expects the US date format makes everything so simple. I don't think I've ever read anything else that told me outright that was the case!

I have successfully deployed his SQLDate function. I declare strDateStart and strDateEnd variables as public variables. In the cmdOK_Click event on the criteria form (I suppose I could use variant types) ...

strStartDate = SQLDate(txtStartDate)
strEndDate = SQLDate(txtEndDate)

The SQLDate function returns the data from the text boxes in strings in the correct format already wrapped in the '#' delimeters.

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. June 2006.
 
If IsDate(varDate) Then
   If DateValue(varDate) = varDate Then
       SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
   Else
      SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
   End If
End If
 
End Function

Then the report filter becomes...

Me.Filter = "ShiftDate Between " & strStartDate & " And " & strEndDate

I can guarantee the SQLDate function will be the first snippet that is added to any new application I attempt. I will be confused no more!

Thank you, thank you, thank you! You and Allen Browne are my idols.
ML

 

[boblarson]

Glad we could help.