changing the format of dates

[ben via AccessMonster.com]

I store dates in my database in a field with yymmdd as the format. Is it
possible to then have a seperate column that displays the month of the entry
as the 3 letter designator for the month?
For example if the date column reads 071121, can the next column read "NOV"?

thanks for the help
ben

 

[Allen Browne]

If you are storing dates as 6 digits (yymmdd) in a *Text* field, create a
query and type an expression like this into the Field row:
TheMonth: Format(DateSerial(Left([d],2), Mid([d],3,2), Right([d],2)),
"MMM")
Replace the [d] with the name of your field.

Do not create another field in your table for this: doing so would be a
maintenance nightmare. By creating the field in the query, and using the
query instead of the table anywhere you need it (e.g. as the source for a
form or report), you never have to worry about it being wrong.

Of course, the data would be much more reliable if you used a Date/Time
field instead of a Text field. That will prevent nonsense dates such as
month 99 or day 30 of Feburary. It would also be much easier to perform date
math (e.g. days duration between 2 dates), and more efficient to
select/sort/query.

If you are using a Date/Time field, the query expression would be just:
Format([d], "MMM")
or you could even use the Format property of the field in the query (or of
the text box on your form/report.) Set it to:
MMM
or if you want to show the month in brackets after the date all in the one
field:
yy/mm/dd \(MMM\)

 

[bmelv via AccessMonster.com]

Cool thanks,
Im still having trouble with the syntax however it comes up with an error
message telling me there is and invalid number of arguements...I also tried
the other method and changed the format to MMM but it said jan for every
entry then.

thanks again
ben

If you are storing dates as 6 digits (yymmdd) in a *Text* field, create a
query and type an expression like this into the Field row:
TheMonth: Format(DateSerial(Left([d],2), Mid([d],3,2), Right([d],2)),
"MMM")
Replace the [d] with the name of your field.

Do not create another field in your table for this: doing so would be a
maintenance nightmare. By creating the field in the query, and using the
query instead of the table anywhere you need it (e.g. as the source for a
form or report), you never have to worry about it being wrong.

Of course, the data would be much more reliable if you used a Date/Time
field instead of a Text field. That will prevent nonsense dates such as
month 99 or day 30 of Feburary. It would also be much easier to perform date
math (e.g. days duration between 2 dates), and more efficient to
select/sort/query.

If you are using a Date/Time field, the query expression would be just:
Format([d], "MMM")
or you could even use the Format property of the field in the query (or of
the text box on your form/report.) Set it to:
MMM
or if you want to show the month in brackets after the date all in the one
field:
yy/mm/dd \(MMM\)

I store dates in my database in a field with yymmdd as the format. Is it
possible to then have a seperate column that displays the month of the
entry
as the 3 letter designator for the month?
For example if the date column reads 071121, can the next column read
"NOV"?

 

[Allen Browne]

Build the expression a bit at at time.

Try typing just this into the Field row in query design:
Left([d],2)
replacing d with your field name. It should show the year.

Then in the next column, try:
Mid([d],3,2)
It should show the month.

Then in the next column, try:
Right([d],2)
It should show the day.

Once that's all working, try putting it together with:
DateSerial(Left([d],2), Mid([d],3,2), Right([d],2))
If each one works individually, but the whole expression fails, you have
some bad data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cool thanks,
Im still having trouble with the syntax however it comes up with an error
message telling me there is and invalid number of arguements...I also
tried
the other method and changed the format to MMM but it said jan for every
entry then.

thanks again
ben

If you are storing dates as 6 digits (yymmdd) in a *Text* field, create a
query and type an expression like this into the Field row:
TheMonth: Format(DateSerial(Left([d],2), Mid([d],3,2), Right([d],2)),
"MMM")
Replace the [d] with the name of your field.

Do not create another field in your table for this: doing so would be a
maintenance nightmare. By creating the field in the query, and using the
query instead of the table anywhere you need it (e.g. as the source for a
form or report), you never have to worry about it being wrong.

Of course, the data would be much more reliable if you used a Date/Time
field instead of a Text field. That will prevent nonsense dates such as
month 99 or day 30 of Feburary. It would also be much easier to perform
date
math (e.g. days duration between 2 dates), and more efficient to
select/sort/query.

If you are using a Date/Time field, the query expression would be just:
Format([d], "MMM")
or you could even use the Format property of the field in the query (or of
the text box on your form/report.) Set it to:
MMM
or if you want to show the month in brackets after the date all in the one
field:
yy/mm/dd \(MMM\)

I store dates in my database in a field with yymmdd as the format. Is it
possible to then have a seperate column that displays the month of the
entry
as the 3 letter designator for the month?
For example if the date column reads 071121, can the next column read
"NOV"?

 

[bmelv via AccessMonster.com]

Thats all working now. thanks heaps for that been a huge help

ben

Build the expression a bit at at time.

Try typing just this into the Field row in query design:
Left([d],2)
replacing d with your field name. It should show the year.

Then in the next column, try:
Mid([d],3,2)
It should show the month.

Then in the next column, try:
Right([d],2)
It should show the day.

Once that's all working, try putting it together with:
DateSerial(Left([d],2), Mid([d],3,2), Right([d],2))
If each one works individually, but the whole expression fails, you have
some bad data.

Cool thanks,
Im still having trouble with the syntax however it comes up with an error

[quoted text clipped - 39 lines]