Date problem: day/month getting switched on filter

[Neil Chinneck]

Hi everyone,

I have a weird problem, and I cannot work out what's going on.

I have a continuous form (frmEnterCosts) based on a query (qryEnterCosts). I have series of unbound combo boxes to filter various fields on this form, one of which (cboFilterPackingDate) filters the field 'PackingDate', which is a Date field.

There is a command button which takes the contents of each combo box to build the filter string and then applies the filter when it is pressed.

The piece of code within the OnClick event concerning the PackingDate is:

If Not Me.cboFilterPackingDate.ListIndex = -1 Then
  strPackingDate = "[PackingDate] = #" & Me.cboFilterPackingDate & "#" & " AND"
Else
  strPackingDate = ""
End If

My problem is that it doesn't seem to be handling the date correctly. If the date is something like 13/08/2010 (i.e. 13th August) which cannot be confused with another date (i.e. 8th of 13th Month doesn't exist), the filter works fine. If the selected date is, say, 05/07/2010 (5th July), it will filter for 07/05/2010 (7th May). And if the 'reverse' date does not exist in the data, it returns nothing.

I have put the filter string into a text box to see what's happening, and the filter string has the correct date (i.e. matches the combo box) it just filters the wrong date, and I cannot for the life of me figure out where its going wrong. There is nowhere in the database where the date is stored in the U.S format.

I've used Format in the code to try and force it, but I don't think this is the problem.

I would be grateful for any pointers as it's driving me a bit mad!

Thanks

Neil

[Neil Chinneck]

Just to update:

If I open the form and filter on a date (16/09/2010) from the PackingDate field manually, then look at the filter, it looks like this:

((qryEnterCosts.PackingDate=#9/16/2010#))

So I could probably get around it by switching the day/month around in my code, but I do not see why it should be treating the date like this when it filters?

Thanks

Neil

[Stewart Ross]

Hi Neil. The problem is that Access is actually interpreting date literals in the ANSI-standard way, which is indeed mm/dd/yyyy. It is switching the day and month where it is clear that the regional setting is applying, but leaving them where the regional setting can be interpreted as a valid mm/dd/yyyy date.

All is not lost, though, as all you need to do is to force the format of the date to the ANSI mm/dd/yyyy form and all should be well.

Replace line 2 above with:

strPackingDate = "[PackingDate] = " & Format(Me.cboFilterPackingDate, "\#mm/dd/yyyy\#") & " AND"

We have an Insights article on Literal date-times and their delimiters which explains why all this occurs.

-Stewart

[Neil Chinneck]

Thanks very much for the reply; it seems like an odd thing for Access to do, but I guess I'll have to live with it.

Thanks