Query to look at the 3rd letter in a field

[KevinSlater]

Hi, anyone know if it possibe to create a field within a query that looks at a 3 letter number code in a field of a table named "employee master" to see if the last number is an "I" or not. So for example a number code in a field named "Department" is "KTI". I would like to display "indirect" in that field if it contains a "I" in the 3rd letter of the number. And display "direct" if the third letter is a "D" intstead, tried doing IF statements but not sure how to go about it, any help would be great.

 

[neileg]

NewField: IIF(Mid([Department],3,1)="I","indirect",IIF(Mid([Department],3,1)="D","direct",""))

 

[KevinSlater]

Many thanks for your quick reply Neilreg!, that seems to be working great, if possible would you or anyone care to tell me a little about how it works so that i can understand better for another time?, although ive got the table "employee master" displayed in the query how does it know from this code which table to look at (just by the field name "department")? & not really sure what the MID & 3,1 bits do exactly.

 

[ColinEssex]

IIF(Mid([Department],3,1)="I","indirect",IIF(Mid([Department],3,1)="D","direct",""))

What Neil has done is to nest 2 IIF statements and include a Mid() function.

An IIf statement can evaluate the value of a field and the answer is always "Yes" or "No"

The Mid() is a way of looking at the value in the department field and counts for 3 characters starting at character 1 (similar to the Right() and Left() functions)

By including it in the IIf statement is it saying this -

"If the 3rd character (starting at 1) in the department field = "I" its is a yes, that means "Indirect" - if its not an "I" its a No so then read the second IIf and If the 3rd character (starting at 1) in the department field = "D" then it is a yes, that means "direct" - if its not a "D" then its nothing ("") "

It knows which table it is because there's only one field called "Department" otherwise you'd get a conflict error.

As an example, you could have 2 codes A = Apple and B = Banana in a field called "Fruit" an IIF statement to get the description would be

IIf([Fruit] = "A","Apple","Banana")

So as there are only 2 values, if its not "A" (the yes its A) it must be B (the no its not A)

Col

 

[neileg]

Couldn't have explained it better!

If the Department code is guaranteed to have three and only three characters, you could use Right() instead of Mid(). If the third character can only be I or D you could miss out the second test. This would give you:

NewField: IIF(Right([Department],1)="I","indirect","direct")

The original formula will cope with codes of more than 3 characters and with any character at position 3.

 

[KevinSlater]

Thanks very much Colinessex & Neileg for your great explanations, couldnt have done this without your help, feel more happy now i understand what the code is actually doing, ive no doubt what ive learnt here will come in handy in the very near future.