Date criteria in Access report via VBA?

[Andrew Fergus]

Hi everyone

I'm having one of those days where I can't see what I have done wrong but I cannot get this to work.

My VBA script is viewing the date critieria from a form in US format (i.e. mm/dd/yy) when my PC, table, form, query & report have everything set to the international format (dd/mm/yy) using the 'Short Date' option. For example, where I enter a date 01/12/05 (Dec 12th) into an unbound text box, I think the VBA script is reading it as 12/01/05.

Consequently, the script is returning the incorrect records based on the different date format.

Does anyone have any suggestions?

TIA, Andrew

 

[Andrew Fergus]

Well I found this in the help file :

You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.

Hmmm - that's helpful!

These are the offending lines of code :

'Capture the values in the form
tmpEntity = Nz(Me.Entity_ID, 0)
tmpDateFrom = Format(Me.DateFrom, "Short Date")
tmpDateTo = Format(Me.DateTo, "Short Date")

'Do or do not filter the entity on the report based on if one was selected
If tmpEntity = 0 Then
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= #" & tmpDateFrom & _
    "# AND [Received_Date] <= #" & tmpDateTo & "#"
Else
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Entity_ID_Link] = " & tmpEntity & _
    " AND [Received_Date] >= #" & tmpDateFrom & _
    "# AND [Received_Date] <= #" & tmpDateTo & "#"
End If

Is there any way around this?
A

 

[SydneyGeek]

Hi Andrew, use a custom function to reformat the dates for you --

Function fnSQLDate(sDate)
    If IsDate(sDate) Then
        fnSQLDate = "#" & Format(sDate, "mm/dd/yyyy") & "#"
    
    End If
End Function

To use in your code, you would change this...

If tmpEntity = 0 Then
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= #" & tmpDateFrom & _
    "# AND [Received_Date] <= #" & tmpDateTo & "#"

to this...

If tmpEntity = 0 Then
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= fnSQLDate(tmpDateFrom) AND [Received_Date] <= fnSQLDate(tmpDateTo)"

Denis

 

[Andrew Fergus]

Hi Denis

Thanks for the speedy reply. Your suggestion worked perfectly. I had tried using the format function in the SQL statement but I was using the non-US format so it didn't work. I was about to resort to setting the criteria back in the query but I prefer the VBA solution (it gives me more options when using the query and report for other purposes). I actually modified the code to include the Format line in the report criteria so it looked like this instead :

DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= #" & Format(tmpDateFrom, "mm/dd/yyyy") & _
    "# AND [Received_Date] <= #" & Format(tmpDateTo, "mm/dd/yyyy") & "#"

Thanks very much
Andrew

 

[SydneyGeek]

Hi Andrew,

You're welcome

Denis

 

[DDonnachie]

So i take it that Microsoft have no plans to fix the way Access handles dates then

 

[Andrew Fergus]

Hi David

Not that I'm aware of. I also suspect it isn't even on the MS list of things to 'fix' given MS is a US-based company and they would use, what appears to us, as backward dates. Unfortunately you have to use a workaround when using international date formats in VBA SQL but the workaround provided by Denis, and my slight modification of its application, seem to work ok. But it is definitely a trap for those of us who use international date formats.

Andrew

 

[SydneyGeek]

Yep, the Americans reckon their way is the way to go. With dates, that is a pain.

Another option that I have found to work, is converting the date to a Double and doing the ranges on that. So...

...Where [SomeField] Between CDbl(tmpDateFrom) And CDbl(tmpDateTo)

Denis

 

[DDonnachie]

Managed to get it going with the Format$ command, so solved this one for now.

Cheers guys

 

[VicRauch]

I think I once suggest that someone use the format with the month name, but still in international format, like 15-Nov-2006 and as I remember it worked. Seeing as how I'm in the U.S., I'm not sure how to test it. But I do know that if it did work, the part that I liked was that the date would always show the way you would want it to show.