Issue with if statement using multiple criteria

[Agodf007]

I am having trouble using the following code in a query. I keep getting an #error can someone please help me out?

Myresult: nz(iif([result]<[RDL],"<"&[RDL],[result]),"NA")

 

[sneuberg]

Your expression appears syntactically correct so I can't see what could be wrong. Could you upload your database?

 

[JHB]

I am having trouble using the following code in a query. I keep getting an #error can someone please help me out?

Myresult: nz(iif([result]<[RDL],"<"&[RDL],[result]),"NA")

Depending from where you're in the world, then "," should be replaced by a ";".

Myresult: nz(iif([result]<[RDL][B][COLOR=Red];[/COLOR][/B]"<" & [RDL][B][COLOR=Red];[/COLOR][/B] [result])[B][COLOR=Red];[/COLOR][/B]"NA")

 

[Agodf007]

We use commas here. I am unable to provide a db as I am on a secure network and the db is large.

 

[JHB]

Check that both fields are the same type, number or text.

 

[sneuberg]

With an expression in a query you don't get much help from Access on figuring out what's wrong. In about five to ten minutes you can create an environment where you will have the debugging tools that you normally have for VBA code. Here are the steps you can do to debug this.

1. Click on the query that this expression is in to select it.
2. In the ribbon CREATE tab click on Form in the Forms section. Access will create a form with the query as the record source
3. Switch to design view and add a button to the form. Click cancel on the wizard if it comes up.
4. Save the form. The name doesn't make any difference as this is just for testing and will be deleted.
5. Right click on the button and click Build Event
6. In the Choose Builder select Code Builder and click OK.
7. In the subroutine add the line: Debug.Print nz(iif([result]<[RDL],"<"&[RDL],[result]),"NA")
8. Switch back to the Object view (View, Object or Shift F7 or Alt F11 on new versions)
9. Switch to Form view.
10. You can select a record which you which to test and then click the button. You may get an error message that will tell you what's wrong.
11. Switch back to code view to see the result. (CREATE Tab, Visual Basic, Alt F11 on newer versions)

With this setup you can now add Debug.Print statement to see the value of the fields, for example you could add

Debug.Print [result]
Debug.Print [RDL]

to see the values of these field for the record selected. Of course you can also see them in the form.

 

[Agodf007]

for some reason, result wouldn't work if it was being used in the criteria. However, numeric result did so this result is fixed.

Myresult: nz(iif([numericresult]<[RDL],"<"&[RDL],[result]),"NA")