Writing and IIf statement in Access?

R

Roger Drews

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])
 
B

Barry Gilbert

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
 
F

fredg

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 ?
 
J

Jason

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
 
R

Roger Drews

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])
 
R

Roger Drews

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])

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 ?
 
R

Roger Drews

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])
 
J

Jason

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])
 
R

Roland

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
 
J

John W. Vinson

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],"")
 
R

Roland

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

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],"")
 
R

Rhonda

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])
 
J

John W. Vinson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top