IIF statement doesn't work like I want it too.

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

Guest

I have a query that gathers info from two tables: FeeNum Table & FeeCosts
Table

In the FeeNum Table I have a Field FeeNum which has all numbered Fees

In my query I have an IIF statement for the FeeNum Field
IIF([FeeNum]=63,63,0)

What I want is: if there is not a feeCost for FeeNum=63 Show FeeNum=0 which
has a FeeCost=$0.00

What happens is I get both FeeNum=63 and feeNum=0 or
if feeNum=63 doe not have a FeeCost, I get nothing!
 
Hi Sally,
If I understand you correctly, you need to create a new column to display.

select IIF([FeeCost] = 0, 0, [FeeNum]) as NewFeeNum
from MyFeeTable
where FeeNum = 63;


What I want is: if there is not a feeCost for FeeNum=63 Show FeeNum=0 which
has a FeeCost=$0.00
Click to expand...


Sally said:
I have a query that gathers info from two tables: FeeNum Table & FeeCosts
Table

In the FeeNum Table I have a Field FeeNum which has all numbered Fees

In my query I have an IIF statement for the FeeNum Field
IIF([FeeNum]=63,63,0)

What I want is: if there is not a feeCost for FeeNum=63 Show FeeNum=0 which
has a FeeCost=$0.00

What happens is I get both FeeNum=63 and feeNum=0 or
if feeNum=63 doe not have a FeeCost, I get nothing!
Click to expand...
 
Sorry, I did not make it clear....I have a query, in that query is a Field -
FeeNum. I am looking for only one FeeNum. 63.....(in the Criteria section of
that field I have the IIF Statement....) If that FeeNum does not exist I get
nothing on my query and nothing on my report. BAD. So I added a FeeNum "0"
in the table with a FeeCost of $0 so if FeeNum did not equal 63 than equal
0... which would allow the query to put a record on the report - zero dollars
- GOOD. Hope that helps

JL said:
Hi Sally,
If I understand you correctly, you need to create a new column to display.

select IIF([FeeCost] = 0, 0, [FeeNum]) as NewFeeNum
from MyFeeTable
where FeeNum = 63;


What I want is: if there is not a feeCost for FeeNum=63 Show FeeNum=0 which
has a FeeCost=$0.00
Click to expand...


Sally said:
I have a query that gathers info from two tables: FeeNum Table & FeeCosts
Table

In the FeeNum Table I have a Field FeeNum which has all numbered Fees

In my query I have an IIF statement for the FeeNum Field
IIF([FeeNum]=63,63,0)

What I want is: if there is not a feeCost for FeeNum=63 Show FeeNum=0 which
has a FeeCost=$0.00

What happens is I get both FeeNum=63 and feeNum=0 or
if feeNum=63 doe not have a FeeCost, I get nothing!
Click to expand...
Click to expand...
 
Back
Top