Date/time field type issues - Access 2003

[dawnd3]

I have a field in a table that is a Date/Time datatype and the format is mm/yy. The data that I transfered over was mm/yy. However, Access decided to add the dd behind the scenes. So, say the record says 12/02 (for Dec 2002) when you click on the field it say 12/01/02. Worse yet, if I type in 12/02 it converts it to 12/02/05. This is quite frustrating. I have the same exact field in another database and somehow I got it to accept mm/yy as the entry and the data stored. However, I did have to change all the existing records because it added the dd when I imported the data in. I can't seem to see any difference in any of the properties. Neither have input masks. the only real difference may be the format the date was before it was brought into access.

Please help me to understand how access stores date and time fields so that I can properly use the mm/yy format without frustration.

Thank you,

Dawn

http://www.calliopeconsulting.net

 

[SkipVought]

Dawn,

MS takes strings that look like dates and attempts to parse into Year, Month & Day, THEN converts Year, Month & Day to a DateSerial Value which is a NUMBER, like today is 38392.

The "rules" that MS uses depends on your Local Date/Time settings in COntrol Panel. In your case, entering 12/02 1) since you did NOT include a year, MS ASSUMES the current year 2) MS assumes m/d as the input format. So MS parsed with these assumptions and gets 2005, 12, 2 and converts to a DateSerial Value in the cell...

=DateSerial(2005, 12, 1)

which is 38392 and is ormatted in accordance with the field settings.



Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[dawnd3]

Thanks Skip. I still feel lost. Is there anything I can do to use mm/dd in a field without it adding the year to it? Why does it work now in one database but not in another when they are both formatted the same way?

Thanks so much,

Dawn

http://www.calliopeconsulting.net

 

[SkipVought]

Exactly what data was entered in the other database, when was this data entered and exactly what data value is stored (as opposed to what is displayed)

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[dawnd3]

I enter 12/05 for December 2005 and 12/05 is stored. When I first transfered it over, it said 12/05 but would say 12/01/05 if I clicked in the cell, but I went through and changed it to say 12/05 and it is fine. And the new records are accepting mm/yy as well.

Thanks Skip

Dawn

http://www.calliopeconsulting.net

 

[pbrodsky]

You cannot really enter mm/yy into access, as far a I know. A date is ALWAYS a month, a day, and a year, because it is stored just as a number representing the number of days from some starting point. That is how skip got 38392. When you enter 12/05 now, it seems correct because Access puts in the current year by default, so you get 12/05/2005, which displays as 12/05 if you format it as mm/yy. The format only changes how you see the date, not how it is stored.
If you really want to enter values as mm/yy you probably need to use some code.
How are you entering values? In a form or directly into a table?

 

[dawnd3]

Maybe using 2005 was a bad example. I can enter 12/02 for December 2002 and it works just as well. In the database where it works, I can enter the data as I want it but I can't figure out what I did differently. Meaning it gets entered as mm/yy and it gets stored as mm/yy. In the one that isn't working I enter mm/yy and it converts it to mm/yy but uses my yy as the day and 2005 as the year. So if I enter 05/01 for May 2001, I get 5/05 for May 2005 and 05/01/05 is stored.

Dawn

http://www.calliopeconsulting.net

 

[WaltLukeIII]

In My experience it has always been easier to enter the full date and round to the begining of the month if the day is not important and then format the field to display the relevent data. for instance you want to wee MM/ YY

I would enter MM/01/YY for the date each time. and then format it to display MM/YY whenever I need it.

it is more work but I have not found a simple way around it. Since access stores the date in the manner that Skip explained

Walt III
SAElukewl@netscape.net

 

[SkipVought]

Did you read and comprehend what I posted???????????

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[dawnd3]

Not really at first, but I think I am getting it.

Thank you Skip and Walt!

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Oh, and thank you too pbrodsky!

Dawn

http://www.calliopeconsulting.net