Concatenate IIF statements in the expression builder

Posted on 2006-06-07
Medium Priority
Last Modified: 2008-02-01
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.
Question by:carlosm06
  • 2
  • 2
LVL 43

Accepted Solution

TimCottee earned 2000 total points
ID: 16851977
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
LVL 65

Expert Comment

ID: 16852024
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*"


Author Comment

ID: 16852032
You're a genious Tim!
LVL 65

Expert Comment

ID: 16852037
ah, dont worry. Ive just seen the sample data, I missed that the 1st read

LVL 43

Expert Comment

ID: 16852050

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-.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question