Problem in Query set-up. 1

[awl]

In the following portion of my SQL, I am having a problem in getting the appropriate results. If [PEPolice]=True, I want the job title to be "Police" and if [PEDriver]=True, then I want the job title to be Drivers. This part displays the appropriate results. However, if neither are true, then whatever is in the other remaining fields, I want that particular job title displayed. In my results, I do get Police and Drivers, however in all the other records, I get a –1. I am puzzled as to what the correct formula is. Any suggestions would be helpful. The source is coming from tblMaster Data List.

TypeOfJobTitle: IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",[PSWSPJobTitle] Or [PPEJobTitle] Or [OEJobTitle] Or [DeployJobTitle]))

 

[lespaul]

So if it's not PEPolice or PEDriver, will only ONE of these:

PSWSPJobTitle
PPEJobTitle
OEJobTitle
DeployJobTitle

Have data?

For instance, there's never a chance that both PPEJobTitle AND DeployJobTitle will have data?



Leslie

 

[PHV]

Have you tried this ?
TypeOfJobTitle: IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",NZ([PSWSPJobTitle]) & NZ([PPEJobTitle]) & NZ([OEJobTitle]) & NZ([DeployJobTitle])))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[awl]

Lespaul: Yes, if it is not PEPolice or PEDriver, then I want one of the remaining four job titles. (In rethinking this situation, it will have to be those entries that do not equal "NA" or those records by error that the user deleted the NA and forgot to place an entry into that job title.)

PHV: Thank you for your suggestion. It does work except I’m getting all four, job title entries. Explanation: On my input form, I have 6 types of exams. The first two are Police and Drivers. The user just has to place a check mark in whichever applies. With the other 4 types of exams, the user places a check mark in whichever type of exam is appropriate, in addition to entering the job title for that particular exam. The default for each job title is NA. The result that I am getting now is: NANANACPA Officer; which is combining all the 4 job titles, whereas only CPA Officer should be displayed. Those records for Police and Drivers are displayed correctly. The combination of 4 job titles affect only for those records not Police or Drivers. I hope my explanation is sufficient! Thanks.....

 

[PHV]

Try this, but I suggest a custom public function as so deeply embedded IIf becomes unreadable...:
TypeOfJobTitle: IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitle]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTitle]<>"NA",[OEJobTitle]),[DeployJobTitle])))))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[awl]

PHV: Thanks very much. I had to complete the last portion of the [DeployJobTitle] and "Others" at the end to make it work. In summary:

TypeOfJobTitle: IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitle]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTitle]<>"NA",[OEJobTitle],IIf([DeployJobTitle]<>"NA",[DeployJobTitle],"Others"))))))

Thanks very much. Curtis..