Date Format Issue (Access 2003) 2

[krissrites]

Hi there,

I have a problem with the Date Format in Access. I have a form linked to a back end table that has a date field set to 'General Date' format.

The date format is set for MM/DD/YYYY. The problem arrives when Access automatically flips the MM with the DD.

For example if I type 04/01/2007 for April 1st, when I tab away from the field, it will switch the MM with the DD and 04/01 becomes 01/04, or January 4th.

Does anyone know why this is happening and how I can fix this?

Thanks

Chris

 

[dhookom]

Have you checked the Regional settings on your PC?

Duane
Hook'D on Access
MS Access MVP

 

[krissrites]

I haven't but I'm in a new location and these machines are pretty locked down. I don't think I would have the access to change that. I'll try n take a look.

 

[krissrites]

Actually I was able to get into Regional Settings and make changes, but there's nothing to change.

The date format contained was 04/01/2009 meaning April 1st, which is what I want.

On my form in Access, if I type 04/01/2009, as soon as I navigate away from that field, it changes to 01/04/2009.

 

[dhookom]

I would check for code that might be running in the after update event of the control. Perhaps some programmer is playing an April Fools joke on you ;-)

Duane
Hook'D on Access
MS Access MVP

 

[krissrites]

No April fools joke. I'm the only programmer working on it. I haven't implemented any code to muck with the date formatting. That's kind of the nice thing about having the data format set at the table level.

Obviously if I type a day greater than 12, it knows how to handle it. Its clear then which value is a day entered and which is a month.

for some reason it keeps changing the order in which you type a day and a month. You can trick it by typing it backwards. For example I can type 01/04/09 and it will change to 04/01/09 which is April 1st and what I want. But its too easy to mess up a date entry and the data integrity will go out the window.

 

[dhookom]

I never enter anything in the format property at the table level. I might format it on the form control. I would also check to see if you have anything set in the input mask property. This is another property that I never use.

Duane
Hook'D on Access
MS Access MVP

 

[krissrites]

I don't have an input mask set. I toyed with it a bit to see if it would protect the data input, but it was performing the same way, swapping month and day.

I only have the Data Type set at the Table level. I want to store date values, so I selected Date/Time. I think you're suggesting that I don't set the Data Type to Date at table level. I guess I'll store it as text then, and try to format at Form level.

Thanks for you response. I will post my results.

 

[PHV]

I guess I'll store it as text then
NO !!!
A date is a date !

 

[krissrites]

Yes I agree, I don't want to store it as text, I want to store as date. That's the only formatting I have set. At the table level, its data type is set to Date/Time. General Date as format.

I just spoke with a colleague. I asked him to run it on his computer, because this little database application has exsisted for over a year and we've never seen this problem. I got him to run it and he's not having this date problem.

So I think its system related and has nothing to do with how I have the form or table set up. I went into Regional settings on my XP machine, but the date format listed within reflects what I want. Month/Day/Year.

has anyone seen this type before? Every time I enter data into this date field it will flip it. So if I enter 04 (April)/02(Day)/09(Year), it will flip month and day and become 02/04/09. If I go back to the field and change 09 to 10, it will once again flip the month and day to become 04/02/2010. It just constantly reverses the month and day.

 

[krissrites]

Resolved.

Thanks for your help guys.

I changed the Regional Language settings to (English) US, and set the location as US as well and the problem went away.

Don't ask me why, because when I had location set to Canada, it had a Date format of M/D/Y. But the problem is solved and that's all I'm concerned with.

Hopefully others will find this thread if they're experiencing the same aggravating issue.

Thanks again!!

Chris