Update Query Not working

[ffejeff224]

Okay so I have a field named Vendor Claim # that I need to be updated via a prompt. Currently I am using an update query with the following IIf statement in the Update To: IIf([vendor claim #] Is Null,[What is the Vendor Claim # - This is NOT CLIENT CLAIM #],[vendor claim #] & "/" & [What is the Vendor Claim # - This is NOT CLIENT CLAIM #])

If the field is null I want it to update what I type in, but if it already has a value I want to add to that with a / in between the two values.

I don't want it to update the vendor claim # if the value I put in is the same as what is already there so I put in the following criteria: <>[What is the Vendor Claim # - This is NOT CLIENT CLAIM #]

The problem I am running into using this query is that it won't update the field if the Vendor Claim # is null. I don't know what I am doing wrong so please help me.

Thanks.

 

[pbaldy]

Try

([vendor claim #] + "/") & [What is the Vendor Claim # - This is NOT CLIENT CLAIM #]

 

[ffejeff224]

Just a quick update. I took out the Criteria and the query updates fine. The problem is that I don't want it updating if current value matches the one that I enter into the promt. Does anyone know a way to acheive this?

 

[pbaldy]

Sounds like you want the criteria back. Perhaps:

WHERE [vendor claim #] <> [Your text here] OR [vendor claim #] Is Null

 

[ffejeff224]

Alright... adding the where clause worked for now. I had tried [vendor claim #]<>[my statement], but Access kept removing the [vendor claim #] and leaving the rest. I thought it would work with just the <>[my statement], but for some reason it wasn't working properly. Thanks for the help I didn't even think about using the WHERE operator. I will update if I have any more problems as I am testing this out.

 

[pbaldy]

No problem; post back if you get stuck.