IIF works on form, not in Query

[DDishShop]

I have this formula that works on a form. It's in an unbound text box.

IIf([FRRotorsReplaced]="New",DLookUp("[FrontRotorPart]","[BrakePartsGroup]","[AssetGroup]='" & [AssetGroup] & "'"),0)

However, if I copy and paste this into the Field line of a new column in a Query, and give it a title like FRRotorPart: then I assumed this would work:

FRRotorPart: IIf([FRRotorsReplaced]="New",DLookUp("[FrontRotorPart]","[BrakePartsGroup]","[AssetGroup]='" & [AssetGroup] & "'"),0)

But all I get is an #Error. If I remove the IIF portion then the dlookup works just fine. What am I missing?

 

[Beetle]

Does the field FRRotorsReplaced exist in the query?

 

[DDishShop]

Yes, in the column directly to the left of the expression. When I run the query the value for FRRotorsReplaced shows as "New".

Just to clarify, if the result of the query is <> "New", then the result of the expression is 0. But if its "New", I get the #Error.

 

[Gasman]

What type is AssetGroup?, string or numeric ?

 

[DDishShop]

It's a string. The Dlookup portion works fine. I even tried a separate IIF:

IIF([FRRotorsReplaced]="New","Good","Bad")

If the value is "New", I get the #Error. If it's anything other than "New", I get "Bad".

 

[Gasman]

Is [FRRotorsReplaced] a lookup field at all?
What if you try another field to test the if on?

 

[isladogs]

According to this article written for Access 2013 , 'new' will become a reserved word at some point so maybe this has already happened.
https://msdn.microsoft.com/en-us/library/office/jj249061.aspx

Using reserved words can cause unpredictable behaviour so I suggest you replace one or more records with 'New Item' or similar and see if that fixes the issue
Also as this is a text field, I suggest replacing 0 in the iif false part with e.g an empty string "".

EDIT As pointed out since, I hadn't noticed this was a field value rather than a name so my point was off topic here.

 

[Gasman]

I've just tried

SELECT IIf([Num]="NEW","Yes","No") AS expr, Test.Num
FROM Test;

and it works fine.

Try the table and query in a new DB?

 

[DDishShop]

Gasman: Yes, it is a lookup field with the options of New, Used, Warranty. So instead of "New", I tried 1. I tried the IIF using a non-lookup field and I get the same results - #Error.

I even tried it with a checkbox value:

FRRotor: IIf([BrakeFlush]=-1,DLookUp("[FrontRotorPart]","[BrakePartsGroup]","[AssetGroup]='" & [AssetGroup] & "'"),0)

Ridders: New is the value, not a field name, so that shouldn't cause an issue with reserved words.

 

[DDishShop]

I just don't understand how these formula's work just fine in a text box on a form, but not in the query that the form is based on. I did multiple compact and repairs but no change. It's only the IIF function that's giving me a headache!

 

[Pat Hartman]

The alias for the calculated field CANNOT duplicate the name of any field in any of the tables/queries in the query. Try changing the name of the calculated field.

 

[Gasman]

I just don't understand how these formula's work just fine in a text box on a form, but not in the query that the form is based on. I did multiple compact and repairs but no change. It's only the IIF function that's giving me a headache!

That is not the case. I created the formula

SELECT IIf([Num]="EFT","Yes","No") AS expr, Test.Num
FROM Test;

as that was what I had in a table to start with.

Then when Colin mentioned the NEW problem, I updated the table replacing EFT with NEW, changed the formula to match and it still worked.

Have you tried a new DB with just what you need to test it.?
I have no idea as to why it does not work for you at present, but certainly if they are lookup fields you have to test for the 'real' value, not what you see?.

People here recommend not to use them and after my first use of them, not knowing any better at the time, I have not used them since.

People have had weird errors with corruption, so I'd at least try another DB just to try and isolate where the fault may be.?

 

[JHB]

I have a suspicion, does the value "New" appear in the table from where the field [FRRotorsReplaced] is in?
Show your query and a printscreen from the query result.
Or even better, post your database with some sample date + name of the query in which you've the problem.

 

[Gasman]

JHB,

The O/P has mentioned that is is a lookup field when I asked.

I have a suspicion, does the value "New" appear in the table from where the field [FRRotorsReplaced] is in?
Show your query and a printscreen from the query result.
Or even better, post your database with some sample date + name of the query in which you've the problem.

 

[arnelgp]

If it cines from a lookup then FRRotorsReplaced is numeric. Check the numeric value of "new" on the table and use that value.

 

[Galaxiom]

According to this article written for Access 2013 , 'new' will become a reserved word at some point so maybe this has already happened.

LOL. Anything in a string or a value in a field will not constitute the use of a reserved word, even if it was "DAO.Database".

Can you imagine having to tell a customer, "Sorry your surname is a reserved word so you will have to pick another name."?

Reserved words are mainly about object names and variable names. There is considerable paranoia such that some developers won't use them anywhere. Worrying about them inside a string takes that paranoia to a new level.

Truth is that it is fine to use reserved words provided the context is understood. Obviously you can't add a member to an object if it already has a member by the same name. However, for example, a Controls collection in a custom Class is perfectly fine because its context is defined by the class object and it won't interact with the Controls collection in an Access object.

 

[Galaxiom]

Doing this in code is the wrong approach. The logic should be managed in the query, retrieving what is to be displayed by joining the [BrakePartsGroup] to the table through another table.

Domain functions are woefully inefficient in queries. It is vary rare that they cannot be managed as part of the query which is far more efficient.

The table names also make me strongly suspect the data structure isn't what it should be.

BTW Iif() in a query is a different function from Iif() in VBA so it is quite possible that it works in one scenario and not the other.

 

[isladogs]

LOL. Anything in a string or a value in a field will not constitute the use of a reserved word, even if it was "DAO.Database".

Can you imagine having to tell a customer, "Sorry your surname is a reserved word so you will have to pick another name."?

Reserved words are mainly about object names and variable names. There is considerable paranoia such that some developers won't use them anywhere. Worrying about them inside a string takes that paranoia to a new level.

Truth is that it is fine to use reserved words provided the context is understood. Obviously you can't add a member to an object if it already has a member by the same name. However, for example, a Controls collection in a custom Class is perfectly fine because its context is defined by the class object and it won't interact with the Controls collection in an Access object.

I know .... I was brain dead when I wrote that post and have now added a correction

 

[Gasman]

I know .... I was brain dead when I wrote that post and have now added a correction

Well you got me.

 

[Galaxiom]

I know .... I was brain dead when I wrote that post and have now added a correction

Enjoying some of your favourite poison for Easter?