Update Query (1 Viewer)

access17401

Registered User.
Local time
Today, 08:32
Joined
Aug 24, 2017
Messages
33
I have an update query amending information, from tblver to tbltan in the query I want to add something but am not sure if it will work, or if i have the right code.

The field i want to update = CRcode
This sits in table = tbltan

I am updating other fields with the new information coming from = tblyer

Query
Field = CRcode
Table = tbltan
Update to = IIf([tblver].[Rcode] Not "08","",[tbltan].[CRcode]="7")

So i want it so for the records from tblver that we are updating if the field Rcode is anything other then 08 or blank then the field CRcode will equal 7, otherwise nothing happens.

Does this sound right? Have i written it right?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,169
If you Join the two table youre query is correct
 

access17401

Registered User.
Local time
Today, 08:32
Joined
Aug 24, 2017
Messages
33
They are joined, so that's perfect. I won't be able to test it until tmrw but was eager to see.

I guess my skills are improving, even if its a little bit
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,169
We all are learners. Good luck.
 

access17401

Registered User.
Local time
Today, 08:32
Joined
Aug 24, 2017
Messages
33
So i tried to implement the above code this morning as part of the update query and i receive this error = the expression you entered contains invalid syntax, without a corresponding operand, any thoughts

Field: CRcode
Table: tbltan
Update to: IIf([tblver].[Rcode] Not "08","",[tbltan].[CRcode]="7")
 

isladogs

MVP / VIP
Local time
Today, 15:32
Joined
Jan 14, 2017
Messages
18,186
So i want it so for the records from tblver that we are updating if the field Rcode is anything other then 08 or blank then the field CRcode will equal 7, otherwise nothing happens.

error = the expression you entered contains invalid syntax, without a corresponding operand

You need to use <> instead of Not in the query expression
Or reverse the values and use = which is easier to follow

Also you are putting this expression in the Update to row of the query designer for the field CR code. So you don't need to reference that field in the expression

Lastly you have your expression back to front
The syntax is iif(expression, true part, false part)

Rcode must be text datatype to have values like 08
If CRcode is also a text datatype, then you need either of these:

Code:
Update to: IIf([tblver].[Rcode] <> "08","7","")
Code:
Update to: IIf([tblver].[Rcode] = "08","","7")

If CRcode is a number datatype, you need one of these
Code:
Update to: IIf([tblver].[Rcode] <> "08",7, Null)
Code:
Update to: IIf([tblver].[Rcode] = "08",Null,7)
 
Last edited:

Users who are viewing this thread

Top Bottom