Replacement Query

[bladelock]

Hi all,

I have three fields in my table: Date1, Month1, Year1

The data is as show in the Date1 field:
01/01/2007
01/11/2007
01/03/2006
05/02/2007
07/02/2007
07/09/2006
11/09/2007

Now, I want to replace Month1 with "01-Jan" for any Date1 = to "01" and
"05-May" for any Date1 = to "05" and 07-Jul for "07". Also, I want to replace
Year1 with the year of Date1: ex. "2007" = 2007 and "2006" = 2006. Could this
be done?

Thanks again

 

[Duane Hookom]

You could create an update query that would do this. However, it is generally
considered bad practice to store values that are easily calculated. Do you
have a legitimate reason for storing the extra values?

 

[John Spencer]

Yes, it can be done. It is not a good idea since the month and year can
always be calculated on the fly and will always be accurate based on Date1
field. If you put the data into Month1 and Year1 you will always need to be
aware that if you add a record or modify date1 in any way that you will need
to re-enter Month1 and Date1

Expressions
Year(Date1) returns the year Number in the Date1 field
Month(Date1) returns the month number in Date1 field

TO get the month number and short month name you can use
Format(Date1, "mm-mmm")

Year can gotten as a string using
Format(Date1,"yyyy")

Post back and ask for an update query if you really need to store the
information
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bladelock]

Could I have the update query

Yes, it can be done. It is not a good idea since the month and year can
always be calculated on the fly and will always be accurate based on Date1
field. If you put the data into Month1 and Year1 you will always need to be
aware that if you add a record or modify date1 in any way that you will need
to re-enter Month1 and Date1

Expressions
Year(Date1) returns the year Number in the Date1 field
Month(Date1) returns the month number in Date1 field

TO get the month number and short month name you can use
Format(Date1, "mm-mmm")

Year can gotten as a string using
Format(Date1,"yyyy")

Post back and ask for an update query if you really need to store the
information
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

UPDATE YourTable
SET Month1 = Format([Date1],"mm-mmm")
, Year1 = Format([Date1],"yyyy")
WHERE [Date1] is not Null

This is still a bad idea, but it is your choice to do it. Good Luck

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..