Passing dates to a query....

[Blackelise]

I have the following line of code;

DoCmd.OpenReport "SalesList", acViewPreview, , "Taxpoint between #" & Me.FromDate & "# and #" & Me.ToDate & "#", acNormal

Background to the problem:
The controls on the form I use to collect the user input for the dates - FromDate & ToDate - display the date correctly in UK format (ie 04/03/2013 = 04-Mar-2013). I checked by changing the control format to short date to show a three letter month. The date picker also works correctly for these controls.

Me.FromDate & Me.ToDate in the above code seems to hold the correct format when followed in debug mode. The filter field on the report shows - Taxpoint between #11/02/2013# and #04/03/2013#. However the report does not select any correct records when they are number within the criteria.

I tried a test directly within the report’s query by adding - between #11/02/2013# and #04/03/2013# - in the Criteria field and run the query, this worked perfectly even tweaking the dates to test.

After much perseverance and hair pulling I decided to check to see if Access (2010&2007) was reverting to USA date format during the DoCmd.OpenReport method call. So I changed the numbers around in the date field (me.FromDate…), but not the control format, to 02/11/2013 (ie mm/dd/yyyy) and hey presto it worked!!! Change the day number as a test and the relevant records are displayed!

I checked my region settings in Window (7); all is correct. I can’t find any in Access options.

All other date calculations that I use elsewhere appear to work without issue.

Question:
So my question is does anyone know if this is a bug or my misunderstanding or how to force UK date formats in Access or another/better way of passing a date from user input on a form through to a report?

As always your help/advice will be gratefully received.

 

[John Big Booty]

Perhaps the discussion and solutions offered here will give you some pointers.

 

[JHB]

Have you tried Format(Me.FromDate,"mm/dd/yyyy")and ofcause the same for the ToDate.

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

 

[Blackelise]

Thanks both for your advice.

I'll implement the suggestion and report back.

Cheers guys.

 

[Blackelise]

Format(...) does the trick!

Thanks again for helping me out.