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.googlegr oups.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(qryFinalCheckMarks.StudentMark) AS MaxOfStudentMark
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMarks.CurrYear
FROM qryFinalCheckMarks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMarks.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.googlegr oups.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(qryFinalCheckMarks.StudentMark) AS MaxOfStudentMark
SELECT [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName] AS
Teacher, UCase([tblStudent].[LastName]) & " " &
[tblStudent].[FirstName] AS Student, qryFinalCheckMarks.CurrYear
FROM qryFinalCheckMarks
GROUP BY [tblTeacher].[FirstName] & " " & [tblTeacher].[LastName],
UCase([tblStudent].[LastName]) & " " & [tblStudent].[FirstName],
qryFinalCheckMarks.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.googlegr oups.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
(qryFinalCheckMarks) 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([MaxOfStudentMark].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([MaxOfStudentMark].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.googlegr oups.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
(qryFinalCheckMarks) 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([MaxOfStudentMark].[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.googlegr oups.com...

Allen,

When I added a textbox to the report with:

=TypeName([MaxOfStudentMark].[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

[jkearns]

Yes, that's correct, they contain Null values but still show up as
zeros.

I tried changing the format to General Number, and Compacting the
database (AutoCorrect was already off) like you suggested. Still no
dice.

I appreciate your time on this. Please let me know if you think of
anything else it could be.

Thanks,
JK

[Allen Browne]

JK, I"ve never seen any version of Access behave like that.

What version of Access are you using, and what serivce pack? Particularly
can you locate these 2 files on your hard disk, right-click and choose
Properties. Give me the number on the version tab:
msaccess.exe
msjet40.dll
The first is typically under C:\Program Files\Microsoft Office\Office
The second under C:\Windows\System32

--
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.googlegr oups.com...

Yes, that's correct, they contain Null values but still show up as
zeros.

I tried changing the format to General Number, and Compacting the
database (AutoCorrect was already off) like you suggested. Still no
dice.

I appreciate your time on this. Please let me know if you think of
anything else it could be.

Thanks,
JK

[jkearns]

Allen,

I'm sorry, I just figured it out. In the Solutions code that I had
copied from the MSDN, I did not look closely enough one of the
functions that code calls. One of them is called xtabCnulls(varX As
Variant) where it takes varX (the number) and if it is Null, makes it a
zero. I am seeing this now because I was about to post this code for
you to see, and as I was going through it, making comments, I saw what
it did. So, if it makes you feel better, I only saw this because I was
about to tell you about it...

Again, my apologies.
JK