Combo box not producing correct records

[davea300]

Hello

Using access 2003, I have a form which filters it's data based the vlaue selected within a combo box. User selects a date from the combo box (format 12/03/2012 UK format) and then clicks a button which opens a form showing all records with an 'appointment date' field that matches 12/03/2012.

It works for every date I select except for 12/03/2012. I have 21 records with that date but whenenever I try and select them the form appears blank. If I try 13/03/2012 or 14/03/2012 from the combo it shows the mathcing records ok.

Very strange, I thought it may have been a formatting problem as all records with a date of 12/03/2012 were imported from excel but even if I manually add a new record into the table with that date the combo box/form won't display them. I can select 12/03/2012 from the combo but no records display on the form.

This will probably be something stupid or easy to fix but I'm a bit stumped!

 

[pwicr]

Hi Dave,

When I had that problem yesterday, I found that when I clicked on the date field in the table it was drawing from, it had the date AND time on there and therefore wouldn't pull up the information i wanted. I reentered the date in those fields and all worked fine.

 

[JANR]

User selects a date from the combo box (format 12/03/2012 UK format)

No mystery here since Access forces dates to US Format (mm/dd/yyyy) when it compares dates, so by selecting 12. March 2012 Access forces this to 3. December 2012 and you get the wrong records.

This will happens to all comparisons with days less then 13 for all dates since it is no 13. month in a calender. You just let the computer take a guess and that's no good.

There are ways to correct this.

Force a date to US Format:

"... [MyDate] =" & Format([FormDate], "\#mm\/dd\/yyyy\#)

Use the FULL form refrence to the Date control on the from:

"...[MyDate] = Forms!NameOfForm!DateControl"

Or since access dates are really numbers you can convert to an integervalue:

"...[MyDate] = " & CLng([FormDate])

See attached db for some testing, select a date in the combobox and try out the 4 commandbuttons to see if you find the records. You will notice that if you choose a date with a date bigger than 12 all buttons work, but for the rest of the dates only Form Ref , US Dates and Number Conversion will find the records.

Enjoy

PS it's only for users which uses non US dates.

JR

 

[missinglinq]

Everyone working with non-US formatted dates and Access probably should read Allen Browne's short article on the subject:

http://www.allenbrowne.com/ser-36.html

Linq ;0)>