Trying to join month, day and year fields

  • Thread starter Thread starter ed
  • Start date Start date
E

ed

I have 3 separate fields (each currently defined as
text). One contains the Month as one or two digits as
needed. One contains the Year, always as two digits. And,
one contains the Day, always the 1st, as one digit. Using
an update query I'd like to combine the fields and end up
with a date of month/day/year

Any suggestions would be appreciated.
 
Why text, and why in separate fields? In which field do you want to store
the result?

If you want to leave it as text, then place this in the Update To row:

Format(CInt([Month]), "00") & "/01/" & [Year]

If you want to convert it to a true date/time value, use:

DateSerial(CInt([Year]), CInt([Month]), CInt([Day]))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thank you VERY much
-----Original Message-----
Why text, and why in separate fields? In which field do you want to store
the result?

If you want to leave it as text, then place this in the Update To row:

Format(CInt([Month]), "00") & "/01/" & [Year]

If you want to convert it to a true date/time value, use:

DateSerial(CInt([Year]), CInt([Month]), CInt([Day]))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
ed said:
I have 3 separate fields (each currently defined as
text). One contains the Month as one or two digits as
needed. One contains the Year, always as two digits. And,
one contains the Day, always the 1st, as one digit. Using
an update query I'd like to combine the fields and end up
with a date of month/day/year

Any suggestions would be appreciated.
Click to expand...


.
Click to expand...
 
John,

I was needing this very same thing. Just wanted you to know that I
appreciate people like you who answer questions in this newsgroup.

Debbie

Why text, and why in separate fields? In which field do you want to store
the result?

If you want to leave it as text, then place this in the Update To row:

Format(CInt([Month]), "00") & "/01/" & [Year]

If you want to convert it to a true date/time value, use:

DateSerial(CInt([Year]), CInt([Month]), CInt([Day]))

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top