Crosstab report Null and Zeros

[jkearns]

Hello,

I made a report from a crosstab query following the steps onlined in
MSDN's Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get access to show NULL
and 0 values seperately. I've tried the following Format properties in
my text box:

0.0;;0;"" --> Both NULL and 0 values display as 0
0.0;;"";"" --> Both NULL and 0 values show up blank

What I want to happen is if a 0 is entered into the database, for it to
appear as 0 (or 0.0), while if no data is entered into the field, it
shows up blank.

Any suggestions? Duane - are you out there, you seem to have lots of
answers :)

Thanks,
JK

[Allen Browne]

I'm not Duane, but you should see Null as Null without anything in the
Format property, or with the Format set to General Number.

What is the source of these numbers? For example, if the Value in the
crosstab is a Count of something, the count could return a zero where there
are no matches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jk*****@gmail. com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Hello,

I made a report from a crosstab query following the steps onlined in
MSDN's Solutions.mdb example. I now have a dynamic crosstab report
(great!), but with one minor problem. I cannot get access to show NULL
and 0 values seperately. I've tried the following Format properties in
my text box:

0.0;;0;"" --> Both NULL and 0 values display as 0
0.0;;"";"" --> Both NULL and 0 values show up blank

What I want to happen is if a 0 is entered into the database, for it to
appear as 0 (or 0.0), while if no data is entered into the field, it
shows up blank.

Any suggestions? Duane - are you out there, you seem to have lots of
answers :)

Thanks,
JK

[jkearns]

You're right. When I don't set anything in the format property, Null
is Null. BUT, 0 also appears as NULL. Sorry, forgot to mention that
in the first one. The source for the report is below:

TRANSFORM Max(qryFinalChe ckMarks.Student Mark) AS MaxOfStudentMar k
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMa rks.CurrYear
FROM qryFinalCheckMa rks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMa rks.CurrYear
ORDER BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName]
PIVOT [AssName] & " (/" & [TotalMarks] & ")";

I don't use Max for any particular reason though. It could be any
function as I'm not doing any calculation.

Thanks,
JK

[Allen Browne]

I'm confused.

If the student has no marks at all, the query returns Null.
If the highest mark the student got was a zero, the query returns zero.

In the report based on this query, you will therefore get a zero if that was
the student's highest mark, or a null if the value does not apply. You
therefore have both nulls and zeros in the report, and that's what you say
you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<jk*****@gmail. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

You're right. When I don't set anything in the format property, Null
is Null. BUT, 0 also appears as NULL. Sorry, forgot to mention that
in the first one. The source for the report is below:

TRANSFORM Max(qryFinalChe ckMarks.Student Mark) AS MaxOfStudentMar k
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMa rks.CurrYear
FROM qryFinalCheckMa rks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMa rks.CurrYear
ORDER BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName]
PIVOT [AssName] & " (/" & [TotalMarks] & ")";

I don't use Max for any particular reason though. It could be any
function as I'm not doing any calculation.

Thanks,
JK

[jkearns]

Allen,

Sorry, maybe I'm not explaining this well:

What I want:

1.) If there is no mark entered (Null), I want a blank space to appear
in the report.
2.) If the student received a zero (0) as a mark, I want the zero to be
displayed in the report.

I've tried the following text box formats:

- (nothing - default) - the problem with this is that Null values show
up as zeros in the report.
- 0.0;;0;"" - the problem with this is that Null values show up as
zeros in the report.
- 0.0;;"";"" - the problem with this is that if a student receieved a
0, it shows up blank on the report.
- 0.0;;;"" - the problem with this is that Null values show up as zeros
in the report.

Does that makes sense? I've also tried using First, Last, Min, Max,
and Sum functions in my crosstab query with all of these Format
scenerios, with no avail.

Any suggestions?

Thanks,
JK

[Allen Browne]

I think you are looking in the wrong place to solve this by looking at the
text boxes in the report.

What does the query return: Null or Zero? If it does not return both in the
right places, you will not be able to fix it up later in the report.
Therefore the solution lies in the query, not the report.

It's a bit hard to advise beyond that, as the query you posted is itself
based on data returned from another query, but check out what the query
returns.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jk*****@gmail. com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Allen,

Sorry, maybe I'm not explaining this well:

What I want:

1.) If there is no mark entered (Null), I want a blank space to appear
in the report.
2.) If the student received a zero (0) as a mark, I want the zero to be
displayed in the report.

I've tried the following text box formats:

- (nothing - default) - the problem with this is that Null values show
up as zeros in the report.
- 0.0;;0;"" - the problem with this is that Null values show up as
zeros in the report.
- 0.0;;"";"" - the problem with this is that if a student receieved a
0, it shows up blank on the report.
- 0.0;;;"" - the problem with this is that Null values show up as zeros
in the report.

Does that makes sense? I've also tried using First, Last, Min, Max,
and Sum functions in my crosstab query with all of these Format
scenerios, with no avail.

Any suggestions?

Thanks,
JK

[jkearns]

My query returns (or at least appears to return) Nulls in the fields
with no data. Is there a way that I can test to see if a blank field
is really Null?

When I run the crosstab query mentioned above, it is querying a query
(qryFinalCheckM arks) that is getting a subset of all the marks (only
those for semester 2 - this is a school database). The crosstab query
then grabs all the assignments that are listed for semester 2 and
displays them against the Teacher and Student.

Thanks for your help in troubleshooting .

JK

[Allen Browne]

Temporarily add a text box to your report, with this control source:
=TypeName([MaxOfStudentMar k].Value)

That should indicate if the value is null, or is being treated as a string
or as some kind of number.

You could test specifically for null with:
=IsNull([MaxOfStudentMar k].Value)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jk*****@gmail. com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

My query returns (or at least appears to return) Nulls in the fields
with no data. Is there a way that I can test to see if a blank field
is really Null?

When I run the crosstab query mentioned above, it is querying a query
(qryFinalCheckM arks) that is getting a subset of all the marks (only
those for semester 2 - this is a school database). The crosstab query
then grabs all the assignments that are listed for semester 2 and
displays them against the Teacher and Student.

Thanks for your help in troubleshooting .

JK

[jkearns]

Allen,

When I added a textbox to the report with:

=TypeName([MaxOfStudentMar k].[Value])

as the control source, I saw the results as "Single" for every record
that had a number entered into the database (including if a 0 was
entered), and "Null" for the records that did not. These "Nulls" still
appear as "0" in the report though.

Any additional suggestions?

Thanks in advance,
JK

[Allen Browne]

So the report tells us that some records do contain a Null, but the text
boxes still show a zero?

Try setting the Format of the text box to:
General Number

If there were a bad Format set on the field in the table, the report might
be using it. Also, make sure that the Name AutoCorrect boxes are unchecked
under:
Tools | Options | General
and then compact the database to get rid of this junk:
Tools | Database Utilities | Compact
(This could solve the problem if Access is mis-identifying the field with
some previously used name.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jk*****@gmail. com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Allen,

When I added a textbox to the report with:

=TypeName([MaxOfStudentMar k].[Value])

as the control source, I saw the results as "Single" for every record
that had a number entered into the database (including if a 0 was
entered), and "Null" for the records that did not. These "Nulls" still
appear as "0" in the report though.

Any additional suggestions?

Thanks in advance,
JK