Update Query Problem

[Steve Muir]

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.

 

[Pat Hartman\(MVP\)]

I'm sorry, I don't understand the purpose of the year table. You can get
the year from the last maintained date by using the Year function.
Year(LastMaintainedDate)

 

[Klatuu]

First, don't name a field Year. It is an Access reserved word and could
cause Problems. Also don't use Date, Month, Day, Hour, Minute, etc. It can
be unpleasant. I always suggest naming conventions that will avoid that ever
happening to you. Here is a link to the most used naming conventions, The
Leszynski/Reddick Guidelines for Microsoft Access:

http://www.dhdurso.org/articles/Access-Naming-Conventions.pdf

To update your year field. you need to add it to the query. Then in the
Update To row underneath year (notice the problem here, we need to use the
Year function, so change the field name. To avoid confusing you, I wll call
it THE_YEAR.

Year([Last_Maintainted_Date) - 2000

Notice that 2001 is year 1, 2002 is year 2, so any of those years - 2000
will give the the right answer.

And, one more thing --- Change the names.

 

[Steve Muir]

sorry, I should have made myself clearer. The "year" field does not actually
contain year value such as 1987 or 87. It is a year number on a rotation
from 1 to 5 where year 1 runs between x date and y date. etc
It appears on form as a combo box with the values 1 to 5 in the drop down.
Just unsure as to whether I can update a combo box which uses a table as its
data source.
The reason for needing this done is that 20000 plus records need the "year"
field populated with 1,2,3,4 or 5 depending on where the "LastMaintainedDate"
lands in specified date ranges. It is not simply a matter of picking up the
year from the date (ie. 31/05/02 means year = 02. 31/05/02 could be year 5
for example)

 

[Steve Muir]

Thanks for the help. And just for the record I have inherited this shambles
of a database and am currently sorting it out!! I will not use the current
field names for long. thanks for the link to naming conventions though

First, don't name a field Year. It is an Access reserved word and could
cause Problems. Also don't use Date, Month, Day, Hour, Minute, etc. It can
be unpleasant. I always suggest naming conventions that will avoid that ever
happening to you. Here is a link to the most used naming conventions, The
Leszynski/Reddick Guidelines for Microsoft Access:

http://www.dhdurso.org/articles/Access-Naming-Conventions.pdf

To update your year field. you need to add it to the query. Then in the
Update To row underneath year (notice the problem here, we need to use the
Year function, so change the field name. To avoid confusing you, I wll call
it THE_YEAR.

Year([Last_Maintainted_Date) - 2000

Notice that 2001 is year 1, 2002 is year 2, so any of those years - 2000
will give the the right answer.

And, one more thing --- Change the names.

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.