Converting dates from 3-column to single column (1 Viewer)

floridamussels

New member
Joined
Dec 16, 2010
Messages
3
Is it possible to concatenate three fields, each containing either month, day or year into a single field with MM-DD-YYYY format? If so, how?
 

boblarson

Smeghead
Joined
Jan 12, 2001
Messages
32,068
To display in a query you could use

NewFieldNameHere:[MonthFieldNameHere] & "-" & [DayFieldNameHere] & "-" & [YearfieldNameHere]

or you could convert it to a date by using

DateSerial ([YearFieldNameHere], [MonthFieldNameHere], [DayFieldNameHere]

It all depends on what you are planning to use it for. (plus it isn't the best to store dates in that broken out of format unless you absolutely have to.
 

floridamussels

New member
Joined
Dec 16, 2010
Messages
3
Bob - Thanks for your help. I began with a three column format because month and day are sometimes missing for a given record.

I'm unsure how to design the query and where to enter the string you provided. I tried creating a select query from the table containing these fields and entering the string you provided as the "criteria" but ":" is an invalid character. I must admit, I'm a novice when it comes to Access so please forgive my lack of experience.

Thanks again for providing the previous advise.
 

floridamussels

New member
Joined
Dec 16, 2010
Messages
3
Bob - Thanks again. The first option (display in query) worked like a dream. Also wanted to try the second option. Do I enter this command in the field area too? And will this create a new field with the converted date? or will it convert the 3-column format to a single column? I need to keep the existing 3-column format for other applications.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom