Convert text month to alpha

**rfcomm2k** · 09-03-2010, 10:55 AM

I have a database with dates stored as 2 digits yy, mm, dd in three separate text fields.
I wish to produce a report that will convert the mm field to the alpha equivalent, i.e.August = 08 or May = 05.

**ConneXionLost** · 09-03-2010, 11:52 AM

Two options.

One:

Add a lookup table like:

Please Login or Register  to view this content.

and join it to your month field.

Two:

Rebuild your date, then take the Month text:

Please Login or Register  to view this content.

Cheers,

**Andy Pope** · 09-03-2010, 12:00 PM

If you only want the month name can you not use

Format([monthfield]*30,"mmmm")

Or *29 depending on what start date Access uses.

**ConneXionLost** · 09-03-2010, 12:09 PM

Yes, nice one Andy!

**rfcomm2k** · 09-03-2010, 02:37 PM

Thank you Andy! Although I was trying to embed the formatting in the report field, I instead added a field to the query and use that as the control source for the report field.I added this field: startm: Format([startmonth]*30,"mmmm"). Then in the report, where the month would be I changed the control source for the label to be startm.

Thanks again!