Regional Setting of Date problem in Access 2000

[Liz_I3]

HI
I have a date field in access 2000 called DATE which I have formatted as mm/dd/yy. In one of my subs I have referenced this date field as:

Month([DATE]).

My problem is that this is a multi user database and some user's computers regional date setting in control panel are set as dd/mm/yy therefore instead of selection the month when my Month([DATE)] is run it pickes up the day. So when I am expecting a return of 04/10/05 I get 10/04/05 Is there any way I can code to control the regions setting through access? I was hoping an auto exe maco or sub could automaticly set the regional date setting to mm/dd/yy when ever the db is opened.

My database is on our co. network ,so I have no control over the options selected when a compute is loaded with Windows 2000 pro. The Windows 2000 disk's default date is dd/mm/yy so unless this is change during setup thats what is loaded.

Thanks
L

 

[bmacr]

I would think that it's still picking up the month, but it's just displaying it in a different order. You could format the CONTROL that it's being displayed in as mm/dd/yy in the front end for each client.

I would avoid changing the regional settings for other peoples' computers as it likely wouldn't be appreciated.

Also, avoid using "Date" as a field name as it is a reserved word in Access. It may not be able to destinguish between whether you're referring to your Date field or the Date() function, which returns the current system date.

 

[Liz_I3]

Thanks

But no I do need to reference the Regianal setting. I am in Canada and in our co we use mm/dd/yy as a standard. So there is no problem with me changing or at least referencing and manipulating the short date settings in the regional setting.

I did format my control as mm/dd/yy, but as a stated when the date comes in and the regional settings are dd/mm/yy my control thinks the dd is the mm. .

I was hoping there was a way to reference the regional settings and then withing access change it to be mm/dd/yy. ( even if it was only a temp. change with in access.)
L

 

[bmacr]

Since your Month ([Date]) reference only sees the serial number of the date in your DB, I can only assume that the date has been stored incorrectly, rather than your control not "seeing" it correctly.

It may very well have something to do with the regional settings on each computer causing incorrect data entry. Personally I never use those formats for entering dates, due to this kind of (potential) confusion. I always use at least Medium date format and/or a calendar control to guarantee correct entry. You can still have it display as short date if that's the look you want.

 

[Liz_I3]

So if I change the stored date in a medium format (once I correct it) that would solve my problem? It would not matter what format the regional setting are set at, it would pick the date up correctly?

L

 

[bmacr]

Yes, I believe it should. The important thing is that the dates are entered correctly in the first place. As I mentioned, dates are stored as serial numbers beginning from Dec 30, 1899 and the format is really just how it is displayed. Also, I am assuming that your date field is of Date/Time data type.

Give this a read:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_datetime.asp