Steve said:
Hi all
I need to update a combo box numeric field called "year" in a table called
tblRegionN1. This gets it data from tblYear which just conains the numeric
values 1,2,3,4 and 5. I need to query a date field called "Last Maintained
Date" as follows:
If "Last Maintained Date" is between 01/01/01 and 31/12/01 then "year"
equals 1, If "Last Maintained Date" is between 01/01/02 ad 31/12/02 then
"year" equals 2 etc etc.
I wrote a query to find all year 1 as follows:
Field: Last Maintained Date
Table: tblRegionN1
Criteria: Between #01/01/01# And #31/12/01#
This returned all the correct records, but I do not know how to add to this
query to make it update the "year" field.
Any help greatly appreciated.
The following Query should do it:
UPDATE tblRegionN1
SET tblRegionN1.[year] = Val(Right$(Format$(
[tblRegionN1]![Last Maintained Date]
,"yy"),1));
But... I hope you have some reason for wanting to do what I think you're
describing. Usually, it's poor practice to STORE into a field in a
Table something, such as the year's units digit, that you could easily
CALCULATE. In this case it appears (prima facie) to be especially
clumsy, since it involves a reference to a separate Table, which itself
contains almost nothing (just the values 1, 2, 3, 4, 5). All this
involves extra maintenance that you probably don't need to spend time
doing -- for example, if you find a record for 2005 with a [year] value
of 3, due to some mistake, what do you do about it? If you calculate
the value, this won't happen in the first place.
I imagine that your Table looks like this:
[tblRegionN1] Table Datasheet View:
tblRegionN1ID year Last Maintained Date
------------- ---- --------------------
744782494 4 4/4/2004
20863728 2 7/4/2002
626229703 3 3/3/2003
1235852337 5 11/7/2005
Somebody has to maintain that [year] field. Suppose, instead, that you
delete that field from the Table, so that the Table looks like this:
[tblRegion_NoYear] Table Datasheet View:
tblRegionN1ID Last Maintained Date
------------- --------------------
1235852337 11/7/2005
744782494 4/4/2004
626229703 3/3/2003
20863728 7/4/2002
Then you could define a Query such as this:
[Q_RegionN1] SQL:
SELECT tblRegion_NoYear.tblRegionN1ID,
Val(Right$(Format$(
[tblRegion_NoYear]![Last Maintained Date]
,"yy"),1)) AS year_Digit,
tblRegion_NoYear.[Last Maintained Date]
FROM tblRegion_NoYear
ORDER BY tblRegion_NoYear.[Last Maintained Date];
.... which could be displayed like this:
[Q_RegionN1] Datasheet View:
tblRegionN1ID year_Digit Last Maintained Date
------------- ---------- --------------------
20863728 2 7/4/2002
626229703 3 3/3/2003
744782494 4 4/4/2004
1235852337 5 11/7/2005
Then you could use references to [Q_RegionN1] in place of [tblRegionN1]
and be able to refer to the [year] value (but I called it [year_Digit]
here to make it obvious that it's not the same thing). Queries can be
used most places that Tables can be, and Queries have the advantage that
you can do calculations in them.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
