Why does this Nested IIF statement add AS fields

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!

.

Relevant Pages

  • Re: Why does this Nested IIF statement add AS fields
    ... If all that is supposed to return ONE value then you have improperly set the parentheses to nest all the statements. ... 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 ... I am using ACCESS 2003 and the query type is "make table". ...
    (microsoft.public.access.queries)
  • Re: Why does this Nested IIF statement add AS fields
    ... currentbalance1 to currentBalance2 if currentBalance2 is zero. ... I think what you are trying to do is to set a field - P3Overall Balance ... Access MVP 2002-2005, 2007-2008 ... I am using ACCESS 2003 and the query type is "make table". ...
    (microsoft.public.access.queries)
  • Re: Membership database updates
    ... even added the Query Design Toolbar and no luck - I would ... the Query Type button (it looks like 2 rectangles in 'cascade' view and says ... black down-arrow where you can change your query to an Append or Update ... If you have any current members in your Import list, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Nested IIF Statement syntax(?) problem
    ... I'm trying to write a nested IIF statement to calculate commission for ... If revenue <=0, commission = $0.00. ... On the second I get an "Syntax error in query expression" ...
    (microsoft.public.access.queries)
  • Nested IIF Statement syntax(?) problem
    ... I'm trying to write a nested IIF statement to calculate commission for ... If revenue <=0, commission = $0.00. ... On the second I get an "Syntax error in query expression" ...
    (microsoft.public.access.queries)