Using IF then statement in Update Query

[nstaton]

I have a table called Pole and a table called 2008PoleTreatmentPoles. I want
to update the table called Pole with the data in the table called
2008PoleTreatment Poles only if the date in the Pole table is < the date in
the 2008PoleTreatmentPoles table. Can someone help me in how to set this up.
I already have the query set up to update the one table to the other; I just
don't know what to write for the date criteria. My query looks like this:

UPDATE Pole INNER JOIN 2008PoleTreatmentPoles ON Pole.UID =
[2008PoleTreatmentPoles].UID SET Pole.PoleNumber =
"2008PoleTreatmentPoles!POLENUMBER", Pole.Span_Backpole =
"2008PoleTreatmentPoles!SPAN_BACKPOLE", Pole.OsmosePole =
"2008PoleTreatmentPoles!OSMOSEPOLE", Pole.Inspection_Date =
"2008PoleTreatmentPoles!INSPECTION_DATE", Pole.BirthYear =
"2008PoleTreatmentPoles!BIRTHYEAR", Pole.Year_Treat =
"2008PoleTreatmentPoles!YEAR_TREAT", Pole.Down_Guy_Quantity =
"2008PoleTreatmentPoles!DOWN_GUY_QUANTIT", Pole.Anchor_Quantity =
"2008PoleTreatmentPoles!ANCHOR_QUANTITY", Pole.Height =
"2008PoleTreatmentPoles!HEIGHT", Pole.Class = "2008PoleTreatmentPoles!CLASS",
Pole.Telco = "2008PoleTreatmentPoles!TELCO", Pole.CATV =
"2008PoleTreatmentPoles!CATV", Pole.Fiber = "2008PoleTreatmentPoles!FIBER",
Pole.GPS_Date = "2008PoleTreatmentPoles!GPS_DATE", Pole.Datafile =
"2008PoleTreatmentPoles!DATAFILE";

Your help would be greatly appreciated

 

[John W. Vinson]

I have a table called Pole and a table called 2008PoleTreatmentPoles. I want
to update the table called Pole with the data in the table called
2008PoleTreatment Poles only if the date in the Pole table is < the date in
the 2008PoleTreatmentPoles table. Can someone help me in how to set this up.
I already have the query set up to update the one table to the other; I just
don't know what to write for the date criteria.

This query as written will update the fields to *THE NAMES OF* the fields in
the other table - not the contents of those fields!!! Not what you want at
all. You need to enclose fieldnames in [square brackets], not in "quote
marks".

Try

UPDATE Pole INNER JOIN 2008PoleTreatmentPoles
ON Pole.UID = [2008PoleTreatmentPoles].UID
SET Pole.PoleNumber = [2008PoleTreatmentPoles].[POLENUMBER],
Pole.Span_Backpole = [2008PoleTreatmentPoles].[SPAN_BACKPOLE],
Pole.OsmosePole = [2008PoleTreatmentPoles].[OSMOSEPOLE],
Pole.Inspection_Date = [2008PoleTreatmentPoles].[INSPECTION_DATE],
Pole.BirthYear = [2008PoleTreatmentPoles].[BIRTHYEAR],
Pole.Year_Treat = [2008PoleTreatmentPoles].[YEAR_TREAT],
Pole.Down_Guy_Quantity = [2008PoleTreatmentPoles].[DOWN_GUY_QUANTIT],
Pole.Anchor_Quantity = [2008PoleTreatmentPoles].[ANCHOR_QUANTITY],
Pole.Height = [2008PoleTreatmentPoles].[HEIGHT],
Pole.Class = [2008PoleTreatmentPoles].[CLASS],
Pole.Telco = [2008PoleTreatmentPoles].[TELCO],
Pole.CATV = [2008PoleTreatmentPoles].[CATV],
Pole.Fiber = [2008PoleTreatmentPoles].[FIBER],
Pole.GPS_Date = [2008PoleTreatmentPoles].[GPS_DATE],
Pole.Datafile = [2008PoleTreatmentPoles].[DATAFILE]
WHERE Pole.[datefield] < [2008PoleTreatmentPoles].[datefield];

Substitute the name of the actual date field (which you did not post) for
"datefield" in the WHERE clause.

Back up your database before running this - update queries are a "one way
trapdoor" and if there's some other error in the query you won't be able to
back out, so be sure you TEST YOUR BACKUP before running the query.