Parsing out the month and the year from a date 1

[mohebk]

Hi,
I have a column that has data that looks like this:
5/19/1999
6/4/1998
11/4/2001
12/13/2005

I would like to write an update query that would insert only the month and the year in a new column called PostMMYYYY. The new column would have these values:
51999
61998
112001
122005

Please help(MS Access 2003).

Thanks


Mo

Mo

 

[dhookom]

Can you share the data types of the fields? "looks like" isn't good enough to describe your data.

Can you share the table and field names?

Duane
Hook'D on Access
MS Access MVP

 

[mohebk]

The source field is called PostDate and it is a date field. The target field is called PostMMYYYY and it is a text field.

Thanks

Mo

 

[CWestII]

Try this. Put this in your update query. Replace "FieldName" with the name of your field that contains your traditional dates.

PostMMYYYY: Format([FieldName],"myyyy")

 

[jrbarnett]

Something like:

UPDATE YourTable
SET PostMmyyyy = Format ("Postdate", "mmyyyy")

will do it:

 

[mmogul]

Whether your data is text or a date field, and the field name is DateText:

Month(DateText) -- will give you the month number
Year(DateText) -- will give you the year number

then concatenate the results

month(DateText) & year(datetext)

 

[mohebk]

mmogul's solution worked great. Thank you all for your great input.

Mo

 

[PHV]

Why storing derived/calculated values ?
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886