Results 1 to 8 of 8

Thread: Access Query Expression Builder Concatenate IIF syntax, formula help?

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access Query Expression Builder Concatenate IIF syntax, formula help?

    Hello and thank you in advance if you can help,
    I am trying to create a column in a query that compiles/concatenates information from other columns when certain criteria are met. The problem I am encountering is how to put it in terms of Concatenate IIf, or if that is even valid syntax within the expression builder, within the formula and how to word this so it will work. I have written out, roughly, what I need, but cannot for the life of me figure out the right syntax to pull it off. If you have any advice, I would appreciate input.
    Below is an outline of what I need and my attempt at writing it out. Part of the problem is that I want the fields concatenated with &"; " & between the fields, but only if not "" (blank) as a result of the IIf statement. I think perhaps I should have AND syntax in there, but I am unsure. The [Updated_Non-Atlas_Flag_Result] = "NewFlag", then concatenate IIf according to the subsequent criteria:
    Thanks if you can offer any advice, much appreciated.
    Maggie
    New Flag (Why Flagged):
    IIf([Updated_Non-Atlas_Flag_Result] = "New Flag",
    IIF([PORTAL_TYPE] = "Not Atlas Portal", "Not Atlas Portal", ""
    &"; " &
    IIF([Data Source] = "Small File", "Unvetted Record", ""
    &"; " &
    IIF([Species_Type] = "NonBreeding_Maine", "Not Maine Breeder", ""
    &"; " &
    IIF([Invalid_Code_4_Species] = "Invalid", "Invalid Code for Species", ""
    &"; " &
    IIF([OBS_DATE_ACCEPTANCE] = "OUTSIDE", "Outside safe dates", ""
    &"; " &
    IIF([OBS_Buffer_DATE_ACCEPTANCE] = "OUTSIDE", " Outside Buffer", "")))))))

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    602
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    I'd write a function to return what you want. I do not think you can do it that way as the IIF, just allows True or False tests.
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    welshgasman,
    Thanks for the note, I was just going to post how I resolved my issue. It is not the prettiest way, and I am sure there is probably some beautiful elaborate formula that would get me there, but this approach worked.
    I created 5 columns, referencing five others, so that in each column the only text in it was associated with the output of the test meaning it was flagged.

    Formulas as follows (each # is a column)
    1: IIf([Data Source]="Small File","Unvetted Record; ","")
    2: IIf([Species_Type]="NonBreeding_Maine","Not Maine Breeder; ","")
    3: IIf([Invalid_Code_4_Species]="Invalid","Invalid Code for Species; ","")
    4: IIf([OBS_DATE_ACCEPTANCE]="OUTSIDE","Outside safe dates; ","")
    5: IIf([OBS_Buffer_DATE_ACCEPTANCE]="OUTSIDE"," Outside Buffer Dates","")

    From that, the only data in the column for each was the specific text I wanted associated with the resulting flagged data. I also added the semicolon into the if true part of the statement to make things easier.

    This allowed me to then create another column based on what I needed:

    New Flag (Why Flagged): IIf([Updated_Non-Atlas_Flag_Result]="New Flag",[1] & [2] & [3] & [4] & [5],"")

    There are a hanging semicolons in places, but this worked. It enabled me to put all the reasons a record was considered invalid from five fields into one. This will make it much easier for the review person to evaluate the records.

    Thanks,
    Maggie

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    602
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    Thanks for coming back with your solution.
    I would have just created a simple function. Much easier to my way of thinking.?
    However there is always more than one way to skin a cat in computing, and that is a novel approach.

    Good luck with your project.
    Office 2007
    Access novice. Sometimes trying to give something back

  5. #5
    Board Regular
    Join Date
    Jan 2014
    Location
    Maine
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    welshgasman,
    I know nothing about writing functions. I just googled it, and it is indeed quite interesting. So, from what you are saying, I could write a function (VBA?) within my query to populate a field I create? This wouldn't be in the expression builder within the design view of the query? I may be able to come up with a "novel approach", but I know it may not be the best, or most efficient, way of doing things. Though, without the knowledge otherwise, I sometimes scramble my way through things. My methods sometimes create a lot of extra columns, which, I know, is not a good practice. If you have any advice, I would greatly appreciate it, as I know I will come across something similar in the future.
    Thanks,
    Maggie

  6. #6
    Board Regular
    Join Date
    May 2013
    Posts
    602
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    You would write the function in a module eg
    Code:
    Public Function Showday(pDate as Date)
    ShowDay = WeekdayName(Weekday(pDate))
    End Function
    Then in your query GUI have something like

    DOW:ShowDay(TransactionDate)

    In the SQL it would look like
    Code:
    ShowDay(TableName.TransactionDate) AS DOW
    So all that complicated code would go into a function, be split down so easy to process and understand and return a value.

    Especially handy if you want this value in many places in the DB or a large amount of logic involved like your situation now.

    This example is obviously simplified as I could use that expression just as easy in the query gui directly, but it should show you the method.
    HTH
    Last edited by welshgasman; May 27th, 2019 at 05:22 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    401
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    It can be done as a single expression - the issue with the trailing ";" will still be there (you could still remove it but would need to evaluate each criteria twice - i.e. once to check if the result ends with a ";" and once to return the correct result. Instead of having the 5 expressions, as one:

    Code:
    1: IIf([Data Source]="Small File","Unvetted Record",Null)+"; " & IIf([Species_Type]="NonBreeding_Maine","Not Maine Breeder",Null)+"; " & IIf([Invalid_Code_4_Species]="Invalid","Invalid Code for Species",Null)+"; " & IIf([OBS_DATE_ACCEPTANCE]="OUTSIDE","Outside safe dates",Null)+"; " & IIf([OBS_Buffer_DATE_ACCEPTANCE]="OUTSIDE"," Outside Buffer Dates",Null)
    Note the use of + in some areas and & in others. The plus will not add the "; " if the previous result was Null

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    401
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query Expression Builder Concatenate IIF syntax, formula help?

    Had a thought - if you are still looking at updates on this... one way to get the result without the trailing ";" (only evaluating each IIF once) within an expression would be to use a double space instead of "; " and the use trim inside a replace function.

    Code:
    Expr1: Replace(Trim(IIf([Data Source]="Small File","Unvetted Record",Null)+"  " & IIf([Species_Type]="NonBreeding_Maine","Not Maine Breeder",Null)+"  " & IIf([Invalid_Code_4_Species]="Invalid","Invalid Code for Species",Null)+"  " & IIf([OBS_DATE_ACCEPTANCE]="OUTSIDE","Outside safe dates",Null)+"  " & IIf([OBS_Buffer_DATE_ACCEPTANCE]="OUTSIDE"," Outside Buffer Dates",Null)),"  ","; ")
    Last edited by stumac; May 29th, 2019 at 03:56 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •