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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
809
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
809
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
809
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,099,992
Messages
5,471,869
Members
406,791
Latest member
Crones8

This Week's Hot Topics

Top