Formatting a user settings date to a system settings date for Access

Hi,

I have been trying to look for this on the forums somewhere, but to no avail.

I'm sure this is a common problem for people having to write code to support different locales using Access.

When looking for records (ie when performing an SQL statement with the 'WHERE' in it, Access only returns the system settings locale (ie the US one where short date is read as mm/dd/yyyy), but any date we enter using Date or Now uses the user settings date. Forcing a Format of Now to mm/dd/yyyy does not work as it passes the user settings date.

My question is then, how can I force a date to take the system settings date so no matter what date I have it will always be Access friendly.

E.g: Here's an example

1) User settings short date: d.M.yy

2) Format (Now, "mm/dd/yyyy") returns

8.4.02 and NOT 04/08/2002, which is what Access expects.

Date$ reads the system settings date but can it be used somehow to force a user settings date ???

This is urgent. Many thanks.

Mike

I completely admit that this is the cowards way out, but it has always worked for me.

I haven't used Date/Time fields in years. I always store dates in a Text field, and then format them, as needed, when I bring them out to use in a query.

Again, this is probably not the most professional way of doing it, but it has saved me a lot of pain and suffering over the years.

Just a suggestion. :)

Thanks hack,

This is fine, but how to you make it work for statements such as: SELECT * FROM Sessions WHERE SessionTimeDate > #8.4.02 1:00:00 AM# AND SessionTimeDate < #12.4.02 1:00:00 AM# AND SessionHost = 3

ie looking for records between two dates and times.

Mike

Hi Hack,

Well, I have just spoken with a friend and colleague, and I think I will swiftly dump Access as it's giving too much trouble and grab hold of a copy of MySql.

Thanks anyway.

Mike