If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Writing and IIf statement in Access?
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]) |
#2
|
|||
|
|||
Writing and IIf statement in Access?
It looks like you're missing an opening square bracket toward the end of the
statement. Also, can PT1 ever be less than zero? If not, you can probably eliminate the second IIF. HTH, Barry "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]) |
#3
|
|||
|
|||
Writing and IIf statement in Access?
Barry
Thanks for the information it helped but did not solve my problem. Thank you "Barry Gilbert" wrote: It looks like you're missing an opening square bracket toward the end of the statement. Also, can PT1 ever be less than zero? If not, you can probably eliminate the second IIF. HTH, Barry "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]) |
#4
|
|||
|
|||
Writing and IIf statement in Access?
On Sun, 28 May 2006 11:20:01 -0700, 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]) Well, you've posted this calculation in a table design newsgroup, and you cannot perform calculations in an Access table. However, in a query you can .... NewColumn:IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]0,[PT1]-[IC1]) Or in a Form or Report (using an unbound text control ... =IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]0,[PT1]-[IC1]) Notice the [ bracket placed in front of the second IC1 field (which you were missing in your expression). Make sure the name of this control is not the same as the name of any field used within it's control source expression. What do you want to occur if the value of [PT1] is 0 ? -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#5
|
|||
|
|||
Writing and IIf statement in Access?
Fredg
Although this helped it did not solve my problem, thanks for the input. "fredg" wrote: On Sun, 28 May 2006 11:20:01 -0700, 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]) Well, you've posted this calculation in a table design newsgroup, and you cannot perform calculations in an Access table. However, in a query you can .... NewColumn:IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]0,[PT1]-[IC1]) Or in a Form or Report (using an unbound text control ... =IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]0,[PT1]-[IC1]) Notice the [ bracket placed in front of the second IC1 field (which you were missing in your expression). Make sure the name of this control is not the same as the name of any field used within it's control source expression. What do you want to occur if the value of [PT1] is 0 ? -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Writing and IIf statement in Access?
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]) |
#7
|
|||
|
|||
Writing and IIf statement in Access?
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]) |
#8
|
|||
|
|||
Writing and IIf statement in Access?
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]) |
#9
|
|||
|
|||
Writing and IIf statement in Access?
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]) |
#10
|
|||
|
|||
Writing and IIf statement in Access?
On Fri, 2 Apr 2010 19:17:01 -0700, Rhonda
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 |
|
Thread Tools | |
Display Modes | |
|
|