Criteria expression

[kdbailey]

I want to make an expression field in an access query that takes a look at model numbers from another field, and determine what to write based on if it contains specific letters.



Say my first column is model numbers, and my 2nd column is the expression, if the model number contains "k" I want the expression to show "Large", "j" would show "medium" and "d" would show "small".

Model Number New Column

21753j73 medium
93757k42 large
75831k84 large
72837d78 small

so on and so forth.

[Bob Fitz]

Try:
NewColumn: IIf(Right([Model],1)="k","Large",IIf(Right([Model],1)="j","Medium",IIf(Right([Model],1)="d","Small","")))

[kdbailey]

Didn't come out right, I'll grab some actual model numbers. The actual criteria is if it contains Z -- output is 30, contains X -- output is 25, contains L -- output is 20, contains none of the above -- output is 15



E250DHXSE 25
E90DPLSE 20
E300DCXSE 25

and so on.

[Bob Fitz]

Hardly a surprize that it didn't work if the data and criteria supplied are wrong. Try:
NewColumn: IIf(InStr(1,[Model],"z")>0,30,IIf(InStr(1,[Model],"x")>0,25,IIf(InStr(1,[Model],"L")>0,20,15)))
This assumes that the column holding the model numbers is called Model.

[kdbailey]

Awesome, works perfectly! Thanks!