Dates going wrong!

[branston]

Not quite sure how to explain, but ill do my best.

I have auto dates in a form (for example, when someone presses a button which sends an automatic e-mail, a box is populated with todays date, using the now() function) This box is formatted to show the date as dd-mmm-yy (eg. today would be 12-Oct-09)

This was working fine until about a week ago. As far as i can tell (and the best way of explinaing it is) the date is being picked up in the american format (mm/dd/yyyy) but access THINKS it is in the english format (dd/mm/yyyy). This has resulted in very odd figured being shown - eg today has been coming up as 10-Dec-09.

Does anyone have ANY idea why this might be happening? It isnt happening everywhere i have an automatically populating date which is confusing me even more.

Just a bit worried whats going to happen tomorrow!!

Thank you!

 

[HiTechCoach]

In the control panel of windows, check you regional settings for Date.

Are you using a date/time data type for the field that stores the data?

 

[Varvaroudis]

I don't know if this helps, but I had a similar issue with the date format.
Somewhere I read (and please correct me if I'm wrong) that in VBA code, we must use ONLY the american format mm/dd/yyyy. So, if you want to get a date right, you first have to use the Format command as:
Dim CalcDate as Date
Format(CalcDate, "d/m/yyyy")

Worked for me

 

[dcb]

I hate this issue so much !!!!

One thing I am not really understanding in your question - Is it on the form or the email the issue exists?

I have used a few ways to get around this
Varvaroudis method
Using Date Part
If its an exact copy using Cstr([DateField]) conversion to string (works in queries too btw)
Microsoft Has a function to deal with this - give msn a search

 

[branston]

Thanks for all your suggestions - ill be giving them all a go today.
dcb - the error is in the form (and table) I dont think i have the date showing anywhere on the e-mail.

 

[branston]

Right... I think i may know why its doing it. I have just found an article that says:

'In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.'

I do indeed have '#' around my date... which i think may be why its happening. But the problem is now, is that i dont know how to make the code work WITHOUT these #'s. The code i have is:

CurrentDb.Execute "UPDATE TblPvnMain SET TblPvnMain.EmailDate = #" & Now() & "# WHERE [PvnNo] = " & Me.PvnNo & " AND [Rev] = " & Me.Rev

If i remove the #'s it comes up with an error. Anyone have any ideas how to get around it?

 

[namliam]

You would work with the us format, using the format function to reformat it to the proper us date format...

Or since Now() is actually access native, why substitute it in all all? Why not simply use it in the sql?
"... EmailDate = Now() WHERE ..."

While I have your attention when ever your using sql in VBA, please make sure your sql remains readable at all times.... You do this by storing the sql in a variable more or less like so...

Dim mySQL  as string
mySQL = ""
mySQL = mySQL & " UPDATE TblPvnMain "
mySQL = mySQL & " SET TblPvnMain.EmailDate = Now() "
mySQL = mySQL & " WHERE [PvnNo] = " & Me.PvnNo 
mySQL = mySQL & " AND [Rev] = " & Me.Rev

For this sql perhaps not immediately critical as its not quite that big, but it is a good habit to get into!
Edit: Note the spaces right after the first " to make sure you dont get concatinated sql that will syntax error out.

 

[branston]

Just fixed it:

CurrentDb.Execute "UPDATE TblPvnMain SET TblPvnMain.EmailDate = Now() WHERE [PvnNo] = " & Me.PvnNo & " AND [Rev] = " & Me.Rev

Thank you all for your suggestions- if i hadnt been looking further into them then i wouldnt have stumbled across this answer!

 

[HiTechCoach]

This tip may be useful for handling any regional date formats in criteria

 Format(CDate(Date()), "\#yyyy\-mm\-dd\#")

also see:

Wrong Formatting in Code

 

[gemma-the-husky]

its sql statements that cause the trouble

in sql any ambiguous date seems to get treated as american

so 31/12/09 is always 31st Dec but

3/12/09 will be march 12th, not 3rd December

so in sql, you have to format the date to unambiguously format it as a UK/non US date.

 

[namliam]

CDate(Date())

What?? Surely not??

 

[branston]

HiTechCoach - That was the website i found as well- very usefull!

Thank you all for your continued feedback - its interesting to see whats going on and nice to know i wasnt just going crazy!!

 

[dcb]

You want a interesting test ---- convert a ambig date (07/08/2009 - dd/mm/yyyy regional) to serial and back to date in code!!! that becomes very interesting....

The CDate Function makes sense as the ANSI date yyyy-mm-dd cannot be misinterpreted by the system

You are not mad branston!

 

[namliam]

You want a interesting test ---- convert a ambig date (07/08/2009 - dd/mm/yyyy regional) to serial and back to date in code!!! that becomes very interesting....

dim x as string
x = "07/08/2009"
?format(dateserial ( Right(x,4), mid(x,4,2), left(x,2) ) , "DD MMM YYYY")
07 Aug 2009

Seems to work just fine...
CDate is the beast of beasts that will always make people scratch their heads, simple solution: Dont use it

 

[keirnus]

dim x as string
x = "07/08/2009"
?format(dateserial ( Right(x,4), mid(x,4,2), left(x,2) ) , "DD MMM YYYY")
07 Aug 2009

Seems to work just fine...
CDate is the beast of beasts that will always make people scratch their heads, simple solution: Dont use it

I solved this problem following kinda the same what mailman just posted.

My advice is make your own common function and experiment/explore what mailman posted.

To test, change the OS Date format to all possibilities in the Regional And Language Option settings of your Control Panel.

That's how I tested it...and even tested Japanese OS.

You can give it a try.