Why does this Nested IIF statement add AS fields

J

jacqelynl

Could someone please explain why this happens and a possible solution to the
problem? I am using ACCESS 2003 and the query type is "make table".

This query works within a Select statement. If I use the below code, all is
well.

....IIf([period_status2]="C" And [Month]=2,[current_balance2],IIf(
[Current_balance2]=0,[current_balance2]+[current_balance1],opening_balance))
AS [P3Overall Balance] INTO Phase3Data....

Unfortunately, I need to add the additional code shown below to the query so
that the entire year is coded and I can distribute this database. When I use
the code below, the query saves as shown below, adding an AS statement after
each nested IIF statement.

IIf([Current_balance2]=0,[current_balance2]+[current_balance1], IIf(
[period_status2]="C" And [Month]=2,[current_balance2])) AS Expr1,
IIf([Current_balance3]=0,[current_balance3]+[current_balance2], IIf(
[period_status3]="C" And [Month]=3,[current_balance3])) AS Expr2,
IIf([Current_balance4]=0,[current_balance4]+[current_balance3], IIf(
[period_status4]="C" And [Month]=4,[current_balance4])) AS Expr3,
IIf([Current_balance5]=0,[current_balance5]+[current_balance4], IIf(
[period_status5]="C" And [Month]=5,[current_balance5])) AS Expr4,
IIf([Current_balance6]=0,[current_balance6]+[current_balance5], IIf(
[period_status6]="C" And [Month]=6,[current_balance6])) AS Expr5,
IIf([Current_balance7]=0,[current_balance7]+[current_balance6], IIf(
[period_status7]="C" And [Month]=7,[current_balance7])) AS Expr6,
IIf([Current_balance8]=0,[current_balance8]+[current_balance7], IIf(
[period_status8]="C" And [Month]=8,[current_balance8])) AS Expr7,
IIf([Current_balance9]=0,[current_balance9]+[current_balance8], IIf(
[period_status9]="C" And [Month]=9,[current_balance9])) AS Expr8,
OPENING_BALANCE AS [P3Overall Balance] INTO Phase3Data

What it is supposed to do is follow the IIFs and add the new field [P3Overall
Balance] populated with the data.

Thank you!
 
S

strive4peace

Hi Jacqelyn,

when you create a calculated field in the query design screen, Access
assigns an 'alias' (what to call it) to the new fieldname. you can
change Expr1, Expr2, etc to suit your desires. It is a good idea,
however, to make the calculated fieldname different than any fieldname
in the source tables so Access does not get confused.

If I have a calculated field based on Fieldname and want it to look the
same but be different, I just add an underscore to the end --> Fieldname_

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*
 
K

KARL DEWEY

Each IIF statement results in data output. It had to go somewhere. That
'somewhere must be defined. If you do not define it Access creates a field
name starting with Expr1 and continuing untill all are named.

Are were you expecting each to be individual added records?
 
J

jacqelynl

Thank you for responding. You and Crystal were kind enough to explain what I
already knew. What I didn't know was why THIS query was acting that way.

Actually, in researching the nested iif statement options, I found this. I
had hoped it would work. If the case is true then give me the true, if false,
then false. The month is the key. Shouldn't it run through them all and
when it finds the right month (i.e.4 for April), then perform the appropriate
functions and end.


KARL said:
Each IIF statement results in data output. It had to go somewhere. That
'somewhere must be defined. If you do not define it Access creates a field
name starting with Expr1 and continuing untill all are named.

Are were you expecting each to be individual added records?
Could someone please explain why this happens and a possible solution to the
problem? I am using ACCESS 2003 and the query type is "make table".
Click to expand...
[quoted text clipped - 33 lines]
Thank you!
Click to expand...
Click to expand...
 
J

jacqelynl

Appended...The output should be in the P3Overall Balance field when all is
said and done.
Thank you for responding. You and Crystal were kind enough to explain what I
already knew. What I didn't know was why THIS query was acting that way.

Actually, in researching the nested iif statement options, I found this. I
had hoped it would work. If the case is true then give me the true, if false,
then false. The month is the key. Shouldn't it run through them all and
when it finds the right month (i.e.4 for April), then perform the appropriate
functions and end.
Each IIF statement results in data output. It had to go somewhere. That
'somewhere must be defined. If you do not define it Access creates a field
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
S

strive4peace

Hi Jacqelyn,

I think you have an error in your logic... but then you did not specify
how you calculated the current_balance# fields ... so enlighten us <smile>

Instead of making fields across for current-balance, it would be better
to make related records in another table with that information.

what is the structure of your source data without calculations? Seems
there should be a more direct way to get what you want


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*


Appended...The output should be in the P3Overall Balance field when all is
said and done.
Thank you for responding. You and Crystal were kind enough to explain what I
already knew. What I didn't know was why THIS query was acting that way.

Actually, in researching the nested iif statement options, I found this. I
had hoped it would work. If the case is true then give me the true, if false,
then false. The month is the key. Shouldn't it run through them all and
when it finds the right month (i.e.4 for April), then perform the appropriate
functions and end.
Each IIF statement results in data output. It had to go somewhere. That
'somewhere must be defined. If you do not define it Access creates a field
Click to expand...
[quoted text clipped - 7 lines]
Thank you!
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If all that is supposed to return ONE value then you have improperly set
the parentheses to nest all the statements. And another problem is that
you can only nest IIF statements seven deep. You really should write a
VBA function to handle this.

Even if you could handle all the months there is no need to add
currentbalance1 to currentBalance2 if currentBalance2 is zero.

If you were trying to handle just 2 months your expression would be
something like:
IIf([Current_balance2]=0,[current_balance1],
IIf([period_status2]="C" And [Month]=2,[current_balance2],
IIf([Current_balance3]=0,[current_balance2],
IIf([period_status3]="C" And [Month]=3,[current_balance3],Null))))

I think what you are trying to do is to set a field - P3Overall Balance
- to some value depending on the month ande whether or not PEriod_Status
= "C".

Could you explain in words what you are attempting to accomplish?


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Could someone please explain why this happens and a possible solution to the
problem? I am using ACCESS 2003 and the query type is "make table".

This query works within a Select statement. If I use the below code, all is
well.

...IIf([period_status2]="C" And [Month]=2,[current_balance2],IIf(
[Current_balance2]=0,[current_balance2]+[current_balance1],opening_balance))
AS [P3Overall Balance] INTO Phase3Data....

Unfortunately, I need to add the additional code shown below to the query so
that the entire year is coded and I can distribute this database. When I use
the code below, the query saves as shown below, adding an AS statement after
each nested IIF statement.

IIf([Current_balance2]=0,[current_balance2]+[current_balance1], IIf(
[period_status2]="C" And [Month]=2,[current_balance2])) AS Expr1,
IIf([Current_balance3]=0,[current_balance3]+[current_balance2], IIf(
[period_status3]="C" And [Month]=3,[current_balance3])) AS Expr2,
IIf([Current_balance4]=0,[current_balance4]+[current_balance3], IIf(
[period_status4]="C" And [Month]=4,[current_balance4])) AS Expr3,
IIf([Current_balance5]=0,[current_balance5]+[current_balance4], IIf(
[period_status5]="C" And [Month]=5,[current_balance5])) AS Expr4,
IIf([Current_balance6]=0,[current_balance6]+[current_balance5], IIf(
[period_status6]="C" And [Month]=6,[current_balance6])) AS Expr5,
IIf([Current_balance7]=0,[current_balance7]+[current_balance6], IIf(
[period_status7]="C" And [Month]=7,[current_balance7])) AS Expr6,
IIf([Current_balance8]=0,[current_balance8]+[current_balance7], IIf(
[period_status8]="C" And [Month]=8,[current_balance8])) AS Expr7,
IIf([Current_balance9]=0,[current_balance9]+[current_balance8], IIf(
[period_status9]="C" And [Month]=9,[current_balance9])) AS Expr8,
OPENING_BALANCE AS [P3Overall Balance] INTO Phase3Data

What it is supposed to do is follow the IIFs and add the new field [P3Overall
Balance] populated with the data.

Thank you!
Click to expand...
 
J

jacqelynl via AccessMonster.com

Thank you for your input - These fields are populated by ODBC connections -
no calculations except for my query.
Hi Jacqelyn,

I think you have an error in your logic... but then you did not specify
how you calculated the current_balance# fields ... so enlighten us <smile>

Instead of making fields across for current-balance, it would be better
to make related records in another table with that information.

what is the structure of your source data without calculations? Seems
there should be a more direct way to get what you want

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*
Appended...The output should be in the P3Overall Balance field when all is
said and done.
Click to expand...
[quoted text clipped - 12 lines]
[quoted text clipped - 7 lines]
Thank you!
Click to expand...
Click to expand...
Click to expand...
 
J

jacqelynl via AccessMonster.com

Yes I wish to return ONE value. Reading the 2nd paragraph now does make
sense. I was using an existing database and query that was already in use
for one month. I am trying to adapt it for the balance of the year with no
human input needed. So...here goes again in English with changes.

Concept: If the current balance2 = 0, return current balance1 (the previous
month's balance), else if period_status2 ="C" And Month = 2, current balance2
otherwise give me the value in the field opening balance and place it in the
new field P3Overall Balance. Does this clarify the issue?

Thanks for the help!


John said:
If all that is supposed to return ONE value then you have improperly set
the parentheses to nest all the statements. And another problem is that
you can only nest IIF statements seven deep. You really should write a
VBA function to handle this.

Even if you could handle all the months there is no need to add
currentbalance1 to currentBalance2 if currentBalance2 is zero.

If you were trying to handle just 2 months your expression would be
something like:
IIf([Current_balance2]=0,[current_balance1],
IIf([period_status2]="C" And [Month]=2,[current_balance2],
IIf([Current_balance3]=0,[current_balance2],
IIf([period_status3]="C" And [Month]=3,[current_balance3],Null))))

I think what you are trying to do is to set a field - P3Overall Balance
- to some value depending on the month ande whether or not PEriod_Status
= "C".

Could you explain in words what you are attempting to accomplish?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Could someone please explain why this happens and a possible solution to the
problem? I am using ACCESS 2003 and the query type is "make table".
Click to expand...
[quoted text clipped - 33 lines]
Thank you!
Click to expand...
Click to expand...
 
J

jacqelynl via AccessMonster.com

I got the query to work correctly with all your help! Thank you everybody!

IIf([Current_balance4]=0,[current_balance3],IIf([period_status4]="C" And
[Month]=4,[current_balance4]+[current_balance3]+[current_balance2],IIf(
[Current_balance5]=0,[current_balance4],IIf([period_status5]="C" And [Month]
=5,[current_balance5]+[current_balance4]+[current_balance3]+[current_balance2]
,IIf([Current_balance6]=0,[current_balance5],IIf([period_status6]="C" And
[Month]=6,[current_balance6]+[current_balance5]+[current_balance4]+
[current_balance3]+[current_balance2],IIf([Current_balance7]=0,
[current_balance6],IIf([period_status7]="C" And [Month]=7,[current_balance7]+
[current_balance6]+[current_balance5]+[current_balance4]+[current_balance3]+
[current_balance2],opening_balance)))))))) AS [P3Overall Balance] INTO
Phase3Data
 
S

strive4peace

Hi Jacqelyn,

too bad what you have to work with is not better structured -- then I
guess you have no option other than to use what they report, to ensure
everyone is working with the same numbers

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Brackets as an IIf statement 3
Nested IIf statement use 4
IIf Statement in "Update To" field of Update Query 6
IIf statement in Make-Table Query 1
Need a nested IIF Statement??? 0
IIF Statement or Formatting Issue? 1
using values from a simple listbox to create a SQL IN Statement 3
Why is this returning a Ubound value of zero 1

Top