Access Query Expression Builder Concatenate IIF syntax, formula help?

[Maggie Barr]

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", "")))))))

 

[welshgasman]

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]

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]

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]

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]

You would write the function in a module eg

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

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

 

[stumac]

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:

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]

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.

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)),"  ","; ")