IIF Statement with many Values

[talkiechick]

Hello So I have created an IIF Statement that states:

Description3: IIf([State]="UT" And [Balance]>=1000000,[Description],"Other-In Utah")

The statement works fine. The problem is I need to field to return if the data is above 1000000 and within Utah I need the Table Description to show. If the value is below 1000000 and in Utah I need it to say Other-In Utah. I also need it to show me that if the row is not in Utah to show Out of Utah.

Do i need to combine three IIF statements to accomplish? and how would i go about doing it?

 

[boblarson]

Try this instead:

Description3: Switch([State]<>"UT","Out of Utah",[State]="UT" And [Balance]>=1000000,[Description],[State]="UT" And [Balance]<1000000,"Other-In Utah")

 

[talkiechick]

Thank you so much that worked like a charm.

Now that I have that set up, do you know i could possibly group the returned value? and have it also sum the balance for each group?

 

[boblarson]

You can set up a second query which then has fields like this:

OutOfUtah:IIf([FieldName]="Out of Utah", [FieldWithValue], 0)


OtherInUtah:IIf([FieldName]="Other-In Utah",[FieldWithValue],0)


WhateveDescriptionWas:IIf([FieldName]<>"Other-In Utah" And <> "Out of Utah", [FieldWithValue],0)


And you put the Grouping on using the Sigma button and then set each of those fields to SUM.

 

[talkiechick]

Everything else works except for the last expression. After it is typed it came up with this error. that there was an extra comma without a preceding value or identifer.

 

[boblarson]

Oops it should be:

WhateveDescriptionWas:IIf([FieldName]<>"Other-In Utah" And [FieldName] <> "Out of Utah", [FieldWithValue],0)

 

[talkiechick]

So it all works... but is there no way for the row to group into one line and sum the entire balances?

Thanks for all your help Bob Larson.

 

[boblarson]

So it all works... but is there no way for the row to group into one line and sum the entire balances?

You should have this all on one line if you did what I suggested.

1. You do the original Switch stuff in your first query.

2. You include that query in a NEW query and then each of what I showed becomes a new field in that query.

3. You can then sum those and to sum them all you can use a field like:

MyTotals:[OutOfUtah] + [OtherInUtah] + [WhateveDescriptionWas]

 

[talkiechick]

Bob- Thank you for helping with this. It was driving me crazy and I still a novice with Access.

 

[boblarson]

¡No Problema!