Re: Why does this Nested IIF statement add AS fields
- From: strive4peace <strive4peace2006@xxxxxxxxx>
- Date: Wed, 30 Apr 2008 16:05:33 -0400
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 :)
*
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!
- References:
- Why does this Nested IIF statement add AS fields
- From: jacqelynl
- Why does this Nested IIF statement add AS fields
- Prev by Date: An Inner Join on an Inner Join
- Next by Date: Asterix
- Previous by thread: Why does this Nested IIF statement add AS fields
- Next by thread: RE: Why does this Nested IIF statement add AS fields
- Index(es):
Relevant Pages
|
