MS Access Query nested IIF statement & derived field
In a report, I have a query that supports a flag that should populate a derived field [Specs] when the [Box] includes specific characters.
My nested IIf statement is giving me some trouble...
1. If the first 2 char of [Box] = "TW"
2. If the first 2 char of [Box] = "CR"
3. If the first 2 char of [Box] = "DW"
4. If [Box] = "0WT"
5. If [Box] = "WWT"
Then the derived field should be populated with "TW", otherwise it should be Null.
Here is what I thought the query should read:
Specs: IIf((Left([Box],2)=“TW”) Or (Left([Box],2)=“CR”) Or (Left([Box],2)=“DW”) Or ([Box]=“0WT”) Or ([Box]=“WWT”),”TW”,Null)
When I put it into the query, Access changes it to this:
Specs: IIf((Left([Box],2)=[“TW”])
I am sure that the issue is with a poorly written IIf statement. Any and all help would be appreciated!
Thank You!
KLB
My nested IIf statement is giving me some trouble...
1. If the first 2 char of [Box] = "TW"
2. If the first 2 char of [Box] = "CR"
3. If the first 2 char of [Box] = "DW"
4. If [Box] = "0WT"
5. If [Box] = "WWT"
Then the derived field should be populated with "TW", otherwise it should be Null.
Here is what I thought the query should read:
Specs: IIf((Left([Box],2)=“TW”) Or (Left([Box],2)=“CR”) Or (Left([Box],2)=“DW”) Or ([Box]=“0WT”) Or ([Box]=“WWT”),”TW”,Null)
When I put it into the query, Access changes it to this:
Specs: IIf((Left([Box],2)=[“TW”])
I am sure that the issue is with a poorly written IIf statement. Any and all help would be appreciated!
Thank You!
KLB
SteveZ
Do you have to do this in the query? If this is a stand-alone application it's MUCH easier to do in VBA and pass parameters as a function from the query.
ASKER
The front end of the application is Access and the backend is SQL. Since the report is built on several different tables, it would seem difficult to write this as code. Or perhaps I am not understanding the method that you are recommending. This master report is a critical part of our business process and as such, it contains about 70 fields from 8 different tables. Visually, it is easier for me to keep in a design view.
But I am open to whatever I would need to do to accomplish this.
The bigger problem seems I would not know where to start in your recommendation.
Thanks,
KLB
But I am open to whatever I would need to do to accomplish this.
The bigger problem seems I would not know where to start in your recommendation.
Thanks,
KLB
The query designer will often re-format things and sometimes do it incorrectly. Try it like this:
IIf(Left$([Box],2)=“TW” Or Left$([Box],2)=“CR” Or Left$([Box],2)=“DW” Or [Box]=“0WT” Or [Box]=“WWT”,”TW”,Null)
JimD.
IIf(Left$([Box],2)=“TW” Or Left$([Box],2)=“CR” Or Left$([Box],2)=“DW” Or [Box]=“0WT” Or [Box]=“WWT”,”TW”,Null)
JimD.
ASKER
Thanks JimD.
I did as you typed, and unfortunately, access is still putting [ ] around the quotes "". Therefore access asks me for values for all [ ] when I run the query.
"TW" becomes ["TW"]
"CR" becomes ["CR"]
etc.
I did as you typed, and unfortunately, access is still putting [ ] around the quotes "". Therefore access asks me for values for all [ ] when I run the query.
"TW" becomes ["TW"]
"CR" becomes ["CR"]
etc.
That's odd. Try it like this:
myField:IIf(Left$([Box],2)
JimD.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SteveZ:
This was one of the best answers that I have received on EE. Thank you for the notes and the code.
It was easy to understand and it works perfectly.
I appreciate the time and the efforts.
Cheers,
KLB
This was one of the best answers that I have received on EE. Thank you for the notes and the code.
It was easy to understand and it works perfectly.
I appreciate the time and the efforts.
Cheers,
KLB
You are entirely welcome! and thank you for the kind response ...