Update query does not work when select query does

[rjmccafferty1]

I am trying to update a field (memo) to equal the previous entry for the memo field plus some text and the value of the AltPhone field when the AltPhone field is not empty. When I run it as a select query I get the answer I want. When I change it to an update query, it does not work. Does anyone know why?

My logic is that I try to set a new field name in the query to be the calculation and then make the memo field update to the contents of this new field.

Memo is in the Customer table, Alt Phone is in the actexcel011610 table. Both tables have an OO field (stands for original order - don't ask) which is the join field.

The SQL version (I created this in the Access query graphical interface) is (and which clearly ignores the defining of the calculation for the MemoCombined field):

UPDATE actexcel011610 INNER JOIN Customer ON actexcel011610.OO = Customer.OO SET Customer.[Memo] = [memoCombined]
WHERE (((actexcel011610.[Alt Phone]) Is Not Null));


In the graphical design section, I define the MemoCombined shows as
MemoCombined: IIf([memo]<>"",[memo] & ", Alt phone is " & [alt phone],"Alt phone is " & [alt phone])

When run as a Select query, the above MemoCombined section runs perfectly, with the SQL code as:

SELECT IIf([memo]<>"",[memo] & ", Alt phone is " & [alt phone],"Alt phone is " & [alt phone]) AS MemoCombined, Customer.Memo, actexcel011610.OO
FROM actexcel011610 INNER JOIN Customer ON actexcel011610.OO = Customer.OO
WHERE (((actexcel011610.[Alt Phone]) Is Not Null));



So I then tried to break it into two steps. First I ran a select query so I got the field of MemoCombined calculated. Then ran what seemed a simpler query based on the select query and the Customer table, trying to change [customer].[memo] to the MemoCombined value. And still it did not work. The SQL on this attempt is:

UPDATE Customer INNER JOIN Set_MemoCombined_Value ON Customer.OO = Set_MemoCombined_Value.OO SET Customer.[Memo] = [memocombined]
WHERE (((Customer.Memo) Is Null));

What am I doing wrong? I am not very skilled in SQL and generally rely on the Access graphical interface to set up my queries.

If it helps, the Memo field currently has only about 10 our of 6000 records with entries. And Alt Phone field has 11 entries.

 

[PHV]

What about this ?

UPDATE actexcel011610 INNER JOIN Customer ON actexcel011610.OO = Customer.OO 
SET Customer.Memo = IIf([memo]<>"",[memo] & ", ","") & "Alt phone is " & [alt phone]
WHERE actexcel011610.[Alt Phone] Is Not Null;

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