Discussion Locked

Access and Querying using Dates

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can anyone tell me why access changes the date format when u are querying.

if u enter a date 10/01/2001 (dd/mm/yyyy) in the creteria, the date will be changed to 01/10/2001 (mm/dd/yyyy). Is there a way of preventing this from happening as this isannoying and gives wrong results depending on the person entering the parameters.

regards

5 replies

by john_wills

I am not quite sure of the problem, but I have had similar experiences. Things depend partly on what is in your Windows Settings/Control Panel/Regional Options/Date: by default Access uses the format you have set there. I develop using ISO dates(yyyy.mm.dd) on my PC, as this is natural to the Gregorian calendar. Most of my users have their PCs set to half-backwards numeric(mm/dd/yyyy or mm/dd/yy), more or less natural to the English variant of the Julian calendar. The databases I give them workon their PCs. In the criterion(yes, I know that some fool officially made it plural), I put the date from the screen inside the function DateValue for comparison. Here is an example from my code:
stLinkCriteria = "[BISNumber]=" & Me.BISNumber _ & "and [SessionDate]= DateValue('" & Me![SessionDate] & "')"
If DCount("[BISNumber]", stDocName, stLinkCriteria) = 0 _
Then
DoCmd.OpenForm stDocName, , , , , , Me.BISNumber & ":" & Me.SessionDate Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
I have also successfully put ISO-format date constants in my VB code, but I do not think it would work to put ascending-format constants in and then run with the Windows setting half-backwards.

by shanghai sam

I'm not going into the detail of the previous answer, but have found consistency to be my usual downfall for date formats. My PC is set to dd/mm/yyyy in Regional Settings and every date field has dd/mm/yyyy as their format (when short date is required). For my SQL strings I use a custom "ConvertDateToUS" function. I can't claim to have experienced Access changing my date values (in parameter queries or otherwise) but I'm certainly not expecting it. If Access is changing your dates, then may be it does so to compare date fields - but then it should be converting all dates...
Sorry I can't be more help, but I'd advise re-checking system settings etc

This discussion is closed to new replies.