Left in Iif Statement for Update Query

[allredkj]

I'm trying to perform an update query based on an Iif statement. If the condition is met, then I want to map the left portion of the tax_zip for 5 characters and the same for the right hand portion of the taxp_zip for 4 characters. I'm getting errors, can you tell me where my error is?


IIf([DALRES].[ST_NUM1]=[DALRES].[TAXP_NUM1],Left([DALRES].[TAXP_ZIP],5),"")


IIf([DALRES].[ST_NUM1]=[DALRES].[TAXP_NUM1],Right([DALRES].[TAXP_ZIP],4),"")

Thanks,
Kelly

 

[boblarson]

Hard to help if you don't specify EXACTLY what errors. That is like going to the doctor and saying - "Doc, I have a bad pain. What's wrong?"

 

[allredkj]

Sorry, here is the error message:

The expression you entered has a function containing the wrong number of arguments.

 

[boblarson]

First let's get one working.

Try

IIf(Nz([ST_NUM1],"")=Nz([TAXP_NUM1],""),Left([TAXP_ZIP],5),"")

 

[allredkj]

I received an error, "Undefined function 'Left' in expression".

 

[allredkj]

I think my table is corrupt. Easy way to fix?

 

[boblarson]

Okay, a couple of things.

1. Do you have any objects (including modules, fields, procedures, etc.) that are named LEFT?

2. Do you have any MISSING REFERENCES? Go to the VBA Window and to TOOLS > REFERENCES and see if anything is marked as MISSING.

3. If so, uncheck the reference and close and reopen.

4. If not, uncheck the Microsoft DAO 3.x reference OR the Microsoft Office 12.0 Access Database Engine Object Library reference (12.0 if on 2007, 14.0 if on 2010). Then close the dialog, reopen it and check it again.

 

[allredkj]

The answer to #1 = no

The answer to #2 = I followed your instructions and am receiving the following:
"Your Microsoft Office Access database or project contains a missing or broken reference to the file 'utility.mda'."

 

[boblarson]

You should uncheck it, if it will let you and then close and reopen the database.

 

[allredkj]

I was able to uncheck it, close and reopen and complete the update query successfully. I was also able to write the 2nd query based on the first query. Thank you so much, I've been having a lot of trouble with my tables and databases corrupting and I've been fixing it the hard way by recreating the table and performing updates again. I've wasted days if not weeks doing this. Now I can recover my data quickly thanks to you! I cannot express how thankful I am. You are a GEM!!!!!

Thanks again!
Kelly

 

[boblarson]

Glad we could help.