Multiple if statements (1 Viewer)

[tcaprice]

I have a form with 6 different Job Code fields on it. Each field is a check box, each is a field in the employee table. Each time a person gets promoted, the next Job Code field gets a check mark but the prior check mark still contains the check along with promtion date, amount, etc. I want to do an iff that will allow me to check each of the 6 fields to see which has a check mark and assign the highest level Job Code to a exp1 field. ie

if jobcode6 = -1 currentjob = jobcode6 else if jobcode5 = -1 currentjob = jobcode5 else if jobcode4 = -1 currentjob = jobcode4,etc

once I have the correct value in the currentjob field, I can do my reporting off of that

Any and all help is appreciated.

Thanks.

Tom

 

[RV]

There are easier ways.

1) only set the checkbox to True for the current JobCode
2) select the most recent PromotionDate using Max as this selects the current JobCode

Actually, seems to me you don't need the checkbox...

If you think one of these suggestions can help you out, come back.

RV

 

[Jon K]

Take a look at this thread. There is an example of nesting 6 iif() functions in a query:-
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=36582


RV is quite right. You seem to have used a flat table (i.e. a spreadsheet-like table) to store staff promotion data.

 

[tcaprice]

Jon K and RV,

Thanks to both of you for your replies, I did use the examples supplied and they worked very nicely, problem solved!

Thanks again.

Tom