Concatenate IIF statements in the expression builder

Posted on 2006-06-07
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
    LVL 44

    Accepted Solution

    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

    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

    You're a genious Tim!
    LVL 65

    Expert Comment

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

    LVL 44

    Expert Comment


    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 Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now