Access Query Expression Builder Concatenate IIF syntax, formula help?

Maggie Barr

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top