floridamussels
New member
- Local time
- Today, 00:10
- 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?
Converting dates from 3-column to single column (1 Viewer)
- Thread starter floridamussels
- Start date
boblarson
Smeghead
- Local time
- Yesterday, 21:10
- Joined
- Jan 12, 2001
- Messages
- 32,059
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.
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
- Local time
- Today, 00:10
- 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.
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
- Local time
- Today, 00:10
- 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.
