Writing and IIf statement in Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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])
 
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
 
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])
Click to expand...

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 ?
 
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
 
Jason
Thank you that solved my problem, I am new at using Access and this was very
helpful.

Jason said:
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 said:
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])
Click to expand...
Click to expand...
 
Fredg
Although this helped it did not solve my problem, thanks for the input.

fredg said:
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])
Click to expand...

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 ?
Click to expand...
 
Barry
Thanks for the information it helped but did not solve my problem.
Thank you

Barry Gilbert said:
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 said:
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])
Click to expand...
Click to expand...
 
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 said:
Jason
Thank you that solved my problem, I am new at using Access and this was very
helpful.

Jason said:
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 said:
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])
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
Click to expand...

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],"")
 
Thanks John, both work like a dream

John W. Vinson said:
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
Click to expand...

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],"")
Click to expand...
 
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 said:
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 said:
Jason
Thank you that solved my problem, I am new at using Access and this was very
helpful.

Jason said:
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

:

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])
Click to expand...
Click to expand...
Click to expand...
 
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])))
Click to expand...


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.
 
Back
Top