Query - IIf statement (1 Viewer)

[akb]

I need to write an expression that does this:

If field 1 = WARRANTY and field 2 = 17 then field 3 = WARRANTY GB, If field 1 = WARRANTY and field 2 = 18 then field 3 = WARRANTY N, If field 1 = WARRANTY and field 2 = 19 then field 3 = WARRANTY C, If field 1 = CLAIM and field 2 = 17 then field 3 = CLAIM GB, If field 1 = CLAIM and field 2 = 18 then field 3 = CLAIM N, If field 1 = CLAIM and field 2 = 19 then field 3 = CLAIM C.

Right now I can pull the data for the first set, but I am having a difficult time writing the expression to tell the query to pull the remaining. Is this possible?

EXP: IIf([SALESMAN1]="WARRANTY" And [JOB_TYPE]=17,WARRANTY GB)

Thank you,
Ashley

 

[plog]

You don't need an expression, you need a table:

Field1, Field2, Field3
WARRANTY, 17, WARRANTY GB
WARRANTY, 18, WARRANTY N
WARRANTY, 19, WARRANTY C
.
.
.
CLAIM, 19, CLAIM C

Then you just link the existing table to that new one via Field1 and Field2 and bring in Field3.

 

[akb]

Alright, this makes sense.

However, in order to get my value for this field I am currently telling the query to reference two other fields.

Salesman: IIf([Salesman2]<>"",[Salesman2],[Salesman1])

After that comes the part I inquired about earlier. If Salesman 1 = Warranty and Job Type = 17, then Salesman = Warranty GB.

How do I tell the query to do all of this in one step?

 

[akb]

I think I figured this out. I created a new Query to link the new table I setup and to determine the final salesman value. I will then link this Query to my main Query. Seems to be working.. thanks for your help!

 

[akb]

One more question - now that I have linked the tables the query is pulling all fields (so if there multiple lines in a job order, it's pulling a record for every line in the job). Prior to linking the tables it was only bringing over one line per job. Help...

 

[plog]

No idea. You are going to have to show me with data. Post 2 sets: starting sample data (include table and field names) and then what data should result.

 

[RogerCooper]

One more question - now that I have linked the tables the query is pulling all fields (so if there multiple lines in a job order, it's pulling a record for every line in the job). Prior to linking the tables it was only bringing over one line per job. Help...

Try setting the Unique Values property to Yes.