access doesn't understand LCID and ruins my dates 1

[ethan1701]

Hi,
I have an ASP form which updates an Access 97 DB. One of the things I update is the date and time a record was stored. I do this by inserting now() into a column (TimeStamp). I've found that when the date is before the 10th of the month, access gets confused. Depending on the LCID I use, I could send it the fourth of July, and it will see the seventh of April. Since I'm not using a string in a dd/mm/yyyy format, but rather, I'm using the now() function, I have no idea how to fix this.
Do any of you have a solution to this situation?

-Ethan

 

[RoyVidar]

Hi!

This is dependent on how you update, since I haven't the foggiest on ASP, I'm just assuming you're using some kind of sql string against the database?

When interacting with the Jet thru sql strings, the date needs to be in a US recognizable format, for instance

[tt]...mydate = #" & format(now,"yyyy-mm-dd") & "#...[/tt]

Don't know how regional settings affect an ASP form, but when concatinating Now() into a string in Access, it uses the regional settings (in my case "dd.mm.yyyy" which not just causes confusion, but syntax error;-))

Even if this isn't an ASP forum (think there is one), some of the "offending" code might help, in stead of guessing...

Roy-Vidar

 

[PHV]

As DateTime are number, you may try to use CDbl(Now())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Your problem Ethan is caused by a mismatch between your computer's 'International' setting and JET

JET always tries to interpret a date in the jumbled American mm-dd-yyyy format if it can.

If your machine is set up to use the logical dd-mm-yyyy format then confusion can occur on some dates in the year.

I get round this by always specifying Medium Date in a Format statement as Roy-Vidar describes above. It converts the month to the three letter abbreviation which even JET can't get wrong !

That will only work for Dates though.
If you need the Time component as well then PHV's fix is a good solution because it forces the date into a universally consistant format and maintains the fraction of a day accuracy.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ethan1701]

I used PHV's solution of replaceing now() wth cDbl(now()), and it works great! Thanks!

Now I just have to convert some 500 records to their correct values. any idea how I could do this?

-Ethan