Date format

[bbulla]

Hi,

I have a date field with a format of 'Short - dd/mm/yyyy'. All of the data stored in that field is in that format.

I have a listbox that displays all those dates. I select a date, then click on a button to filter the form by the date selected. Here is my code that fires on the cmd_Click event:

Forms!frmPondSampling.Filter = "RPID = " & Me.cboPondID & " AND Date = #" & Me.lstDate & "#"

Forms!frmPondSampling.FilterOn = True

This works for dates where the dd is more than 12, but it wo't work when the dd is less than 12. I'm assuming it is getting the dd and mm mixed up, but all my data is correct and matches the field type.

What is going on??

 

[pbaldy]

I don't have much experience with the issue, but in VBA dates must be in US format:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069

 

[bbulla]

Wow....that seemed to work, but I have no idea why. If I use:

format(me.lstDate, "MM/DD/YYYY")

, then I can properly query my Date field which has a format of DD/MM/YYYY. That makes no sense to me, but it works.

Thanks!

 

[raskew]

As Paul has pointed out, VBA dates must be in US format, i.e. mm/dd/yyyy. You are correct that Access is confusing days and months when days is <13. If days are >12, Access will generally make the correct interpretation, but has no way of knowing that you're not using US format when the number of days is 12 or less.

Take a look at this MSKB article http://support.microsoft.com/kb/q130514/ to see how Access stores and manipulates dates. I'm assuming that you're storing your dates in date/time data format. If, however, they are stored as text, you've opened up another can of worms, as well as depriving yourself of the use of Access' many built-in date/time functions.

Bob