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?
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 |
#4
|
|||
|
|||
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]) |
#5
|
|||
|
|||
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]) |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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]) |
#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?
Sorry to necro this tread, but I am having a similar problem with a report
that is using several IIf statements within textboxes and I am now trying to add a second possiblity. (there are a total of 14 Households, but only 2 require their departments to be seperated) This is what I had orginally for just 1 household =IIf([HouseholdNumber]=1,"Department: " & [Department],"") I have tried to add a second household using the above suggestion =IIf([HouseholdNumber]=1,"Department: " & [Department],""), IIf([HouseholdNumber]=7,"Department: " & [Department],"") but this fails. I have also tried And and Or before the 2nd IIf |
#10
|
|||
|
|||
Writing and IIf statement in Access?
On Fri, 8 Aug 2008 07:30:01 -0700, Roland
wrote: Sorry to necro this tread, but I am having a similar problem with a report that is using several IIf statements within textboxes and I am now trying to add a second possiblity. (there are a total of 14 Households, but only 2 require their departments to be seperated) This is what I had orginally for just 1 household =IIf([HouseholdNumber]=1,"Department: " & [Department],"") I have tried to add a second household using the above suggestion =IIf([HouseholdNumber]=1,"Department: " & [Department],""), IIf([HouseholdNumber]=7,"Department: " & [Department],"") but this fails. I have also tried And and Or before the 2nd IIf The first argument can be a logical expression. Try =IIf([HouseholdNumber]=1 OR [HouseholdNumber] = 7,"Department: " & [Department],"") You can also use the IN() syntax: =IIf([HouseholdNumber] IN (1,7),"Department: " & [Department],"") -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|