Prev: Headers/Footers in Access Tables
Next: Anyone have a worker scheduling template
From: Rhonda on 2 Apr 2010 22:17
I am doing a if then statement in Access with multiple like statements. I
think I am close, but I am getting a circular reference error...

here my query
1YTDDEPRAMT: IIf([ASSETCLASSID] Like
"*-CR",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*DEPLOYC",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*BASIC-C",[1YTDDEPRAMT]*-1,[1YTDDEPRAMT])))


"Jason" wrote:

> Trust me I know the feeling. I'm still in the "keep beating on it until it
> works" phase of of my Access education. I just knew the answer to your
> problem because I use "IF" a ton in Excel. Anyway, glad I could help.
>
> "Roger Drews" wrote:
>
> > Jason
> > Thank you that solved my problem, I am new at using Access and this was very
> > helpful.
> >
> > "Jason" wrote:
> >
> > > Roger,
> > >
> > > There are 2 problems. One is that you are missing the last part of your 2nd
> > > conditional statment. The second is that you appear to be testingfor more
> > > than 2 conditions.
> > >
> > > Based on what you have written, try this:
> > >
> > > field name: = IIf([PT1]=0,[PC1]-[IC1],[PT1]-[IC1])
> > >
> > > You only need the aditional IIf statement if you are testing for a 3rd
> > > condition:
> > >
> > > field name: = IIf([PT1]=0,[PC1]-[IC1],IIf([PT1]>0,[PT1]-[IC1],[XX1]-[IC1]))
> > >
> > > Just remember that there are 3 parts: 1) the condition you are testing for
> > > 2) value if true 3) value if false false
> > >
> > > "Roger Drews" wrote:
> > >
> > > > I am trying to write an IIf statement to use in a calculation where if PT1 is
> > > > zero it will use PC1 to determine the value. Here is the state I have tired
> > > > but I cannot get it to work. What am I doing wrong?
> > > > IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]>0,[PT1]-IC1])
From: John W. Vinson on 5 Apr 2010 01:14
On Fri, 2 Apr 2010 19:17:01 -0700, Rhonda <Rhonda(a)discussions.microsoft.com>
wrote:

>I am doing a if then statement in Access with multiple like statements. I
>think I am close, but I am getting a circular reference error...
>
>here my query
>1YTDDEPRAMT: IIf([ASSETCLASSID] Like
>"*-CR",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
>"*DEPLOYC",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
>"*BASIC-C",[1YTDDEPRAMT]*-1,[1YTDDEPRAMT])))


You're trying to set the value of 1YTDDEPRAMT based on the value of
1YTDDEPRAMT.

You cannot define a value based on itself.

Use a different fieldname before the colon.

--

John W. Vinson JVinson *at* Wysard Of Info *dot* com
 | 
Pages: 1
Prev: Headers/Footers in Access Tables
Next: Anyone have a worker scheduling template