Access Query Expression Builder Concatenate IIF syntax, formula help?

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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", "")))))))
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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.
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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.
 

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
169
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
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,871
Messages
5,483,440
Members
407,395
Latest member
Sakshine

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top