Using IIF in UPDATE

X

XP

I am trying to execute an UPDATE on a single table that would look something
like:

UPDATE table SET IIF([F1] <> Me!cbxNumber, Me!cbxNumber),
IIF([F2] <> Me!cbxVendor, Me!cbxVendor);"

This syntax fails. Can someone please help me sort this out?

Thanks.
 
X

XP

Sorry that SQL should read:

UPDATE table SET IIF([F1] <> " & Me!cbxNumber & ", " & Me!cbxNumber & "),
IIF([F2] <> " & Me!cbxVendor & ", " & Me!cbxVendor & ") WHERE ID = " &
Me!cbxID & ";"
 
J

John Vinson

I am trying to execute an UPDATE on a single table that would look something
like:

UPDATE table SET IIF([F1] <> Me!cbxNumber, Me!cbxNumber),
IIF([F2] <> Me!cbxVendor, Me!cbxVendor);"

This syntax fails. Can someone please help me sort this out?

Thanks.
Click to expand...

It's not at all clear what you're trying to do. The SET clause should
be a fieldname - and you cannot use an IIF to return the fieldname.

Please explain WHAT it is that you are starting with, in your table
structure and form controls; and what you're trying to accomplish. I'm
sure it can be done, just not this way!

John W. Vinson[MVP]
 
V

Van T. Dinh

The basic syntax for JET SQL UPDATE statement is:

UPDATE table
SET TableField = newvalue,
AnotherTableField = anothernewvalue
WHERE criteria;

Note the equal sign after each Table Field.

For example (from JET SQL Reference in Access Help):

UPDATE Orders
SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK';

In your SQL, I can't see any equal sign for assignment.
 
D

David F Cox

and I cannot see any fields for the expressions to be SET equal to.

If you design the Update query in the QBE grid just put the fields in the
field row and the IIF statements into the "Update to: " row.

The QBE has been writing SQL longer than most of us :->

Van T. Dinh said:
The basic syntax for JET SQL UPDATE statement is:

UPDATE table
SET TableField = newvalue,
AnotherTableField = anothernewvalue
WHERE criteria;

Note the equal sign after each Table Field.

For example (from JET SQL Reference in Access Help):

UPDATE Orders
SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03
WHERE ShipCountry = 'UK';

In your SQL, I can't see any equal sign for assignment.

--
HTH
Van T. Dinh
MVP (Access)



XP said:
I am trying to execute an UPDATE on a single table that would look
something
like:

UPDATE table SET IIF([F1] <> Me!cbxNumber, Me!cbxNumber),
IIF([F2] <> Me!cbxVendor, Me!cbxVendor);"

This syntax fails. Can someone please help me sort this out?

Thanks.
Click to expand...
Click to expand...
 
Top