IIF issues in a Query

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

Guest

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. But It doesnt work. It shows nothing when
only a FeeNum=0 or both when FeeNum=63.
How should I do it?
 
I have never come across a report that didn't return records that I wanted
to show records. Is there a reason you want to show a dummy record? What are
you attempting to accomplish?
 
Hello, I've a pretty good feeling that you probably just have a minor issue
with your query. I'm guessing that the FeeNum field may be coming from a
related child table, and sometimes there is no "activity" for the parent
record, and so it does not appear in any of the reports.

What you need to change, if this is the problem, is the "join type"; In your
query, which shows the two tables, double-click the line that joins them
together, and Join Properties window comes up -- Choose option two or three
so that ALL records from your "parent" table are shown.

For instance, to select all customers, regardless of whether or not they've
made payments, you might do something like this:

SELECT tblCustomers.CustName, tblPayments.PayAmt
FROM tblCustomers LEFT JOIN tblPayments ON tblCustomers.CustomerID =
tblPayments.CustomerID;

Replace the word "left" with "inner", however, and you are back the problem
you are having.

Hope this helps!

--Jon
 
Back
Top