Display blank field in reports

  • Thread starter Thread starter Lin
  • Start date Start date
L

Lin

Hello,

I am creating a report in the following format.

Year 2000 2001 2002 .................
Machine1
MFR1 12 8 19
MFR2 13 15 20
MFR3 8 5 13

Machine 2
MFR1
MFR2
MFR3

In this format if MFR1 has no number for a year, that is not typically
listing in the report. But, I would like to have them display with each
machines eventhough their numbers are zero. Is ther anyway I can do that? Any
adjustments in query.

Any help really appreciate.

Thank you
 
I would do it directly in the report. I assume the numbers are coming from
numeric fields in the report's record source. So for example, the control
source property probably looks something like:
[SomeField]

You can use the Nz function to convert those records with a Null to a Zero
like this:
Nz([SomeField], 0)
 
Do you have a table structure(s) you could share with us? How about the SQL
view of the report's Record Source? Is this the result of a crosstab query or
do you actually have separate fields for separate years?

Do you understand LEFT and RIGHT JOINs?
 
This is the table structure.

Mfr (text) Machine(text) 2000 2001 2002 2003 2004 2005 2006 2007

Mfr1 M1
Mfr2 M1
Mfr3 M1
Mfr1 M2
Mfr2 M2
Mfr3 M2 (Not in the table , the entire row)But I would like to display
them in order for the perfection of the report

I would like to have the report in the following format

M1 Machine
Mfr1
Mfr2
Mfr3
M2 Machine
Mf1
Mfr2
Mfr3 Excatly the same format
Yes I know about Left and right join.


Machine
 
I don't care for a table structure that must be modified each year. This
isn't normalized.

Beyond that, can't you just group by machine then manufacturer? If one of
the fields is null, you can display a zero in the report by setting the
Format property of the text box.
 
Back
Top