Solved: Search on Date field returns wrong format

[Arnold]

Hi

When I search on a date field for #5/7/2005# using the .findfirst method Access is returning a record with "7/5/2005".

some code snipits
dDate = CDate(nCount + 1 - nFirstDay & "-" & Month(Date1) & "-" & Year(Date1)) ' this evaluates to '5/7/2005'

rstCalendar.FindFirst ("date = #" & dDate & "#") ' this finds a record that is '7/5/2005'

however if the date searched for is '20/5/2005' then the correct record is returned. Presumably because 5/20/2005 is not a valid date.

I have date format set to dd/mm/yyyy

Any suggestions would be appreciated.

[OBP]

Check the country code format in Access it looks like it set to US rather than UK style dates.

[Arnold]

I didnt think Access had a seperate setting.

Quote from the help file:
"Microsoft Access can display a wide range of international formats. To help ensure consistency among applications, Microsoft Access uses the regional settings specified by double-clicking Regional Settings in the Windows Control Panel"

besides, all other parts of my application work OK. Dates display correct in tebles & forms. It is just this bit of code that seems to fall over.

[smozgur]

I didnt think Access had a seperate setting.

Quote from the help file:
"Microsoft Access can display a wide range of international formats. To help ensure consistency among applications, Microsoft Access uses the regional settings specified by double-clicking Regional Settings in the Windows Control Panel"

besides, all other parts of my application work OK. Dates display correct in tebles & forms. It is just this bit of code that seems to fall over.

Right, but you are using SQL string with FindFirst and SQL expressions always require mm/dd/yy format (month first). So your search returns wrong date where month number as it expected is less than 13 (right, it will work with 20/05/2005 because it will make the necessary conversion when it thinks that the current syntax won't return a date value).

Please try following code instead, then SQL will get the long value of the date and you don't need to worry about the date settings anymore:

[VBA]dDate = CDate(nCount + 1 - nFirstDay & "-" & Month(Date1) & "-" & Year(Date1)) 'Same as yours, no changing
rstCalendar.FindFirst ("date =" & CLng(dDate))[/VBA]

Suat

[Arnold]

That works like a charm.

Thanks heaps

[Ken Puls]

Hi Arnold, and welcome to VBAX!

Did you know that we have a great little feature where you can mark your own threads solved here? (Thanks to our awesome boardcoders! ) Just follow the instructions in my signature.

I've got this one!