RE: Why does this Nested IIF statement add AS fields

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?

--
KARL DEWEY
Build a little - Test a little


"jacqelynl" wrote:

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: Can an IIf Statement return more than one value
    ... I am new to VBA but would like to continue to learn more. ... best way to open the query on VBA I am guessng an "On Click" Or 'On Focus" ... appears as the RowSource for your combo box (with the RowSourceType set to ... saying that you want the IIf statement to select multiple rows from the ...
    (microsoft.public.access.formscoding)
  • Re: Find - replace automatic
    ... So there was a quote just before the first IIF statement? ... Microsoft Access MVP ... and ran an Update query against that field. ... you can handle the ApprovalStatus updates ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help with a query
    ... You can use the IIf statement in queries: that's an "Immediate If", ... IIf(expr, truepart, falsepart) ... If expr is true, the function returns truepart. ... Is this possible for a query. ...
    (microsoft.public.access.gettingstarted)
  • Re: IIf problem with <>
    ... Where is this IIF statement? ... Is it in the query that is the forms ... a simple filter wont do as there is two other bits of criteria acting apon ... To me this should show all the records if the showComplete tick box is true ...
    (microsoft.public.access.queries)
  • Re: need help with IIF statement
    ... following IIF statement the XTAB query returns the correct ... "system" for the exceptions, but the field is blank for the ... table by putting the select query that returns one column inside the ... Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text - ...
    (comp.databases.ms-access)