Concatenate IIF statements in the expression builder

I have two fields that I am extracting data off from. One static field called DBID which always returns "30FSE". I have another field called Job which returns 3 types of values either "30-05-0001", "30-10-0002" or "30-99-0003". The logic is that I would like to say if the DBID = 30FSE then AG & if JOB like *05* then B or if JOB like *10* then C & right(JOB,3). Concatenation is needed. The result from one record whose DBID is 30FSE and JOB is 30-05-0001 would return this result AGB001.  The result from one record whose DBID is 30FSE and JOB is 30-10-0002 would return this result AGCB002. Can I do something like this in the expression builder of an access query? I have so far: Job Code: IIF([DBID] = "30FSE",[AG]) I cant figure out how to concatenate the other statements.
Who is Participating?
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
Hi carlosm06,

iif([DBID]="30FSE","AG","") & Iif([Job] Like "*-05-*","B",iif([Job] Like "*-10-*","C","D")) & Right([Job],3)

Should do it. Assuming that if you have a job like "30-xx-xxxx" where the xx is not 05 or 10 then you get a D instead of the B/C

Tim Cottee
Tim, why did u put dashes in the wildcards check, do u really need them?
I would of thought it would just be   Like "*05*"    and Like "*10*"

carlosm06Author Commented:
You're a genious Tim!
ah, dont worry. Ive just seen the sample data, I missed that the 1st read

TimCotteeHead of Software ServicesCommented:

I put them in because I guess there is a possibility that one of the job numbers could match either 05 or 10 in some other part of the string. E.g., "30-99-0005" which would match on 05 but not on -05-.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.