So the query does not generate any record for the B type when there is no B?
It is therefore the query that needs work, not the report. An outer join
should solve the problem.
Your table has a lookup field for TypeOfInfo. You probably have a table that
contains the valid choices, so you can use a combo and ensure the user does
not enter an invalid choice. If you do not have such a table, create one. It
just needs one field of type Text, size 1, and marked as primary key. You
will then create a relationship between this lookup table and your main one
(Relationships on the Tools menu.)
Now add the lookup table to your query. You will see a line joining the 2
tables in the upper pane of query design. Double-click this line. Access
pops up a dialog offering 3 choices. Choose the one that says, "All records
from the little lookup table, and any matches from the main table you always
had."
The query will not show both the A and the B, even for the cases where the B
does not exist. It knows about the B value because it is in the lookup
table, even it if it is not in the main table.
If you have criteria on the main table, you will need to change these to
include the case there there is no match. Otherwise you lose the B again.
For example, if you have critiera of:
"USA"
change it to:
Is Null Or "USA"
More info on outer joins and criteria in this article:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
triplespiral said:
Appreciate the help though I'm still a bit lost. maybe a bit more info
will
help.
I have a query that had two fields, one the type of info (A or B) which is
"Group By" and then the second field is the ID number which i have set to
"Total" so currently the query is displaying only
A - 200
where i would like the query (and ultimately) the report to display
A - 200
B - 0
I understand your original advice if i was using a normal count on a
report
- however the report fields are not count(*) as they are the actual field
"CountOf..." so I'm a bit unclear as to how and what to modify
Thanks for your patience!!
L.
Allen Browne said:
Yes, the idea is to test the HasData property of the report.
My count is actually coming from the query - would i use the same in
the
criteria for the field that is being counted?
:
Presumably you have a text box in the Report Footer section, and its
Control
Source is:
=Count("*")
Try:
=IIf([Report].[HasData], Count("*"), 0)
message
I imagine this may have a simple answer but i'm stumped. I have a
report
that does a count of records - how do i get it to show a zero (0) if
there
are no records for that area?
