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

 

[DDonnachie]

At least if the date was wrong it would be more obvious.

 

[Andrew Fergus]

Hi Vic

I expect that would be a way around the problem if the date were hard-keyed or known in advance. If the date / criteria is being extracted from somewhere else, then the techniques described by Denis are the way to go. You could test this by changing the regional settings on your PC via the control panel - in fact, give it a go to see the odd-ball results it throws up when using dates in SQL within VBA.

Cheers, Andrew

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.

 

[SydneyGeek]

As Andrew found,

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.

SQL in VBA is hard-wired for US dates. You have to manipulate the dates to get sensible results in VBA, if your date format is non-US. I just do it by default now, so my code works anywhere.

Denis

 

[VicRauch]

Andrew,
I changed my Regional Options settings to English (New Zealand) and ran the following query and put in the First Date and Last Date as shown in the also following JPGs. If I had not defined these two date parameters as Date/Time, the query output would show just what I keyed in. First, I'll show what the table I was querying looks like:<SCRIPT language=JavaScript src="http://www.theprimarykey.com/js/accHTML.js"></SCRIPT><link rel="stylesheet" type="text/css" href="http://www.theprimarykey.com/css/acchtml.css"/><CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><font color="White">DateText : Table</font></TD><TD CLASS="AccTBInner" align=right >Access 2002/XP</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccHDRMain" align=left >Field1</TD><TD CLASS="AccHDRMain" align=left >DateReceived</TD><TD CLASS="AccHDRMain" align=left >DateClosed</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >4</TD><TD CLASS="AccDataElem" >1</TD><TD CLASS="AccDataElem" >12/09/2007</TD><TD CLASS="AccDataElem" >5/09/2006</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >2</TD><TD CLASS="AccDataElem" >12/09/2006</TD><TD CLASS="AccDataElem" >4/09/2006</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >3</TD><TD CLASS="AccDataElem" >3/08/2006</TD><TD CLASS="AccDataElem" >18/08/2006</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >4</TD><TD CLASS="AccDataElem" >14/03/1940</TD><TD CLASS="AccDataElem" >16/03/1940</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" >
</TD><TD CLASS="AccDataElem" >5</TD><TD CLASS="AccDataElem" >2/09/1945</TD><TD CLASS="AccDataElem" >13/09/1945</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccIEOnlyButton" > 9 </TD><TD CLASS="AccIEOnlyButton" > 3 </TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccIEOnlyButton" > 4 </TD><TD CLASS="AccIEOnlyButton" > : </TD><TD CLASS="AccIEOnlyInnerLeft" > of 5</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 4 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data381786525" CLASS="AccInfoBarInnerRight" >DateText</TD><TD ID="Info381786525" CLASS="AccInfoBarData" > Record Count: 5</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 4 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER><script type="text/javascript">if (navigator.appName != 'Microsoft Internet Explorer') { var a = document.getElementById("currRec"); a.innerHTML = '
'; var b = document.getElementById("IE"); b.textContent = 'Table format has been altered for non-IE browsers '; b.style.fontSize = 10;}</script>As you can see in records 3, 4, and 5, the dates are in dd/mm/yyyy format. Then by putting in the month name, even when the month name was first, or after the day, the query works. Does this type not work for you?

Here is the query in the query grid

The two parameters, First Date and Last Date:

And then the query output:

 

[SydneyGeek]

Vic,

The problem only occurs when you build a SQL string in VBA. If you use the query grid it's fine, but if you used VBA to build SQL with dates, you'd see the same strange results that we have create the work-arounds for.

I don't know why the dichotomy exists, but it's there and we have to work with what we have.

Denis