Date criteria in Access report via VBA?

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :oops:
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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! :unsure:

These are the offending lines of code :
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Andrew, use a custom function to reformat the dates for you --
Code:
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...
Code:
If tmpEntity = 0 Then
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= #" & tmpDateFrom & _
    "# AND [Received_Date] <= #" & tmpDateTo & "#"
to this...
Code:
If tmpEntity = 0 Then
    DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= fnSQLDate(tmpDateFrom) AND [Received_Date] <= fnSQLDate(tmpDateTo)"
Denis
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :
Code:
DoCmd.OpenReport "rptReceipts", acViewPreview, , _
    "[Received_Date] >= #" & Format(tmpDateFrom, "mm/dd/yyyy") & _
    "# AND [Received_Date] <= #" & Format(tmpDateTo, "mm/dd/yyyy") & "#"


Thanks very much
Andrew (y)
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

Hi Andrew,

You're welcome

Denis
 

DDonnachie

Board Regular
Joined
Jul 6, 2006
Messages
153
So i take it that Microsoft have no plans to fix the way Access handles dates then
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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...

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

Denis
 

DDonnachie

Board Regular
Joined
Jul 6, 2006
Messages
153
Managed to get it going with the Format$ command, so solved this one for now.

Cheers guys
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,081
Members
412,763
Latest member
sienweiw
Top