Make field in report show zero and not blank

K

Ken Snell [MVP]

No. In ACCESS 2000 and higher versions, VBA help and regular help are
separated from each other. And sometimes it's confusing which help file has
which info.


--

Ken Snell
<MS ACCESS MVP>


T. W. said:
My apologies, but I don't know VBA. I was putting the Val funtion in
the control source field of the text boxes, and the Nz in the query itself.
Do you think that will produce negative results?


Ken Snell said:
They are listed in ACCESS VBA Help under...well, that depends upon which
version of ACCESS you have :)

For ACCESS 2002, they are under Programming in Visual Basic | Visual Basic
Language Reference | Functions.

--

Ken Snell
<MS ACCESS MVP>


T. W. said:
Where can I find of list of the Cxxx functions (or are they functions)
and
their definitions?


Ah, that means that the 0 coming from the Nz function is being
treated
as
a
text character, not a number. What type of number is returned by the query
if it's not a "null" being turned into a zero? Integer? Long Integer?
Single? Double?

Whichever it is, wrap the Nz function with the appropriate Cxxx
function
to
cast the result in the right data type. For example, if it's supposed
to
be
a Long Integer, use the CLng function in the query's calculated field:

CalculatedField: CLng(Nz([FieldName], 0))
--

Ken Snell
<MS ACCESS MVP>

I now think I know why its not working.

It no longer treats the fields as holding numbers when it hits one
of
the
queries.
I figured that out when I used the Nz function on the query (the query
mentioned in number 3 in the below list).

The Nz function works, and shows zero's in the query, but when I
tell
the
report to add them together (I'm doing a total per record in the same
grouping in the report) it adds it together like a string (0+0 = 00
instead
of just 0)

I assumed it was an issue in the report but adding the fields
together
in
the query (number 3) does the same thing. It won't give me values.

If we can solve that, it should be a problem anymore.

Thanks beforehand.


I'm sorry for continuing to ask questions, but which field are you
using
as
the control source? What is the name of the textbox?

--

Ken Snell
<MS ACCESS MVP>

The Control Source for the Textbox in the report is just the name
of
the
field in the query.

Here's the run down.

1) A regular parameter query is pulling from two or three tables

2) Two Crosstab queries pull from that query: one that counts the
amount
of
workers and the other that sums the total hours.

3) Another parameter query pulls from both crosstab queries so as
to
have
the counts and the sums together. There are joins in this query to
keep
the
records straight.

4) One report pulls from the query in number 3.

5) All properties for all fields holding numeric values have been set
to
Standard with hours holding 2 decimals and count holding 0 decimal
places.

Before you ask, the reason step one is there instead of just
jumping
to
step
two, is to have an accurate count of workers.

Also, none of the Crosstabs will generate zeros when the field is
empty
(I'm
hoping its just me not doing it correctly).


Please post the expression that you're using as the textbox's
Control
Source.

--

Ken Snell
<MS ACCESS MVP>

SELECT [FT - Count].ContName, [FT - Hours].TrdName, [FT -
Count].[Majority
Male], [FT - Hours].[Majority Male], [FT - Count].[Minority Male],
[FT -
Hours].[Minority Male], [FT - Count].[Majority Female], [FT -
Hours].[Majority Female], [FT - Count].[Minority Female], [FT -
Hours].[Minority Female]
FROM [FT - Count] INNER JOIN [FT - Hours] ON ([FT - Count].ContName
=
[FT -
Hours].ContName) AND ([FT - Count].TrdName = [FT - Hours].TrdName);


message
If you're getting Error error in the textbox, then something else
is
wrong
here.

Post the SQL statement of the query from which you're
getting
the
field,
and
post the exact expression that you're trying to use as the
control
source
of
the textbox.

--

Ken Snell
<MS ACCESS MVP>


I tried that. It produces #Error. The textbox has a control
source
that
is
the name of the field from the query its pulling from (or is
that
what
you
meant).

message
So the textbox has a control source that is the name of a
field?
Then
use
this as the control source instead:

=Nz([NameOfField], 0)


--

Ken Snell
<MS ACCESS MVP>

I have 8 TextBoxes that pull from a query.



"Ken Snell [MVP]" <[email protected]>
wrote
in
message
Depends upon why the textbox shows a blank. What is the
control
source
of
the textbox?

--

Ken Snell
<MS ACCESS MVP>

How do I make a TextBox in a report show the number
zero
instead
of
a
blank.

Thank you beforehand.
 
T

T. W.

Thanks Ken,
I really appreciate the help.



Ken Snell said:
No. In ACCESS 2000 and higher versions, VBA help and regular help are
separated from each other. And sometimes it's confusing which help file
has
which info.


--

Ken Snell
<MS ACCESS MVP>


T. W. said:
My apologies, but I don't know VBA. I was putting the Val funtion in
the control source field of the text boxes, and the Nz in the query itself.
Do you think that will produce negative results?


Ken Snell said:
They are listed in ACCESS VBA Help under...well, that depends upon
which
version of ACCESS you have :)

For ACCESS 2002, they are under Programming in Visual Basic | Visual Basic
Language Reference | Functions.

--

Ken Snell
<MS ACCESS MVP>


Where can I find of list of the Cxxx functions (or are they functions)
and
their definitions?


Ah, that means that the 0 coming from the Nz function is being treated
as
a
text character, not a number. What type of number is returned by the
query
if it's not a "null" being turned into a zero? Integer? Long
Integer?
Single? Double?

Whichever it is, wrap the Nz function with the appropriate Cxxx
function
to
cast the result in the right data type. For example, if it's
supposed
to
be
a Long Integer, use the CLng function in the query's calculated field:

CalculatedField: CLng(Nz([FieldName], 0))
--

Ken Snell
<MS ACCESS MVP>

I now think I know why its not working.

It no longer treats the fields as holding numbers when it hits one of
the
queries.
I figured that out when I used the Nz function on the query (the query
mentioned in number 3 in the below list).

The Nz function works, and shows zero's in the query, but when I tell
the
report to add them together (I'm doing a total per record in the same
grouping in the report) it adds it together like a string (0+0 = 00
instead
of just 0)

I assumed it was an issue in the report but adding the fields together
in
the query (number 3) does the same thing. It won't give me values.

If we can solve that, it should be a problem anymore.

Thanks beforehand.


I'm sorry for continuing to ask questions, but which field are
you
using
as
the control source? What is the name of the textbox?

--

Ken Snell
<MS ACCESS MVP>

The Control Source for the Textbox in the report is just the
name
of
the
field in the query.

Here's the run down.

1) A regular parameter query is pulling from two or three tables

2) Two Crosstab queries pull from that query: one that counts
the
amount
of
workers and the other that sums the total hours.

3) Another parameter query pulls from both crosstab queries so
as
to
have
the counts and the sums together. There are joins in this query to
keep
the
records straight.

4) One report pulls from the query in number 3.

5) All properties for all fields holding numeric values have
been
set
to
Standard with hours holding 2 decimals and count holding 0 decimal
places.

Before you ask, the reason step one is there instead of just
jumping
to
step
two, is to have an accurate count of workers.

Also, none of the Crosstabs will generate zeros when the field
is
empty
(I'm
hoping its just me not doing it correctly).


message
Please post the expression that you're using as the textbox's
Control
Source.

--

Ken Snell
<MS ACCESS MVP>

SELECT [FT - Count].ContName, [FT - Hours].TrdName, [FT -
Count].[Majority
Male], [FT - Hours].[Majority Male], [FT - Count].[Minority
Male],
[FT -
Hours].[Minority Male], [FT - Count].[Majority Female], [FT -
Hours].[Majority Female], [FT - Count].[Minority Female], [FT -
Hours].[Minority Female]
FROM [FT - Count] INNER JOIN [FT - Hours] ON ([FT -
Count].ContName
=
[FT -
Hours].ContName) AND ([FT - Count].TrdName = [FT -
Hours].TrdName);


message
If you're getting Error error in the textbox, then
something
else
is
wrong
here.

Post the SQL statement of the query from which you're getting
the
field,
and
post the exact expression that you're trying to use as the
control
source
of
the textbox.

--

Ken Snell
<MS ACCESS MVP>


I tried that. It produces #Error. The textbox has a control
source
that
is
the name of the field from the query its pulling from (or is
that
what
you
meant).

message
So the textbox has a control source that is the name of
a
field?
Then
use
this as the control source instead:

=Nz([NameOfField], 0)


--

Ken Snell
<MS ACCESS MVP>

I have 8 TextBoxes that pull from a query.



in
message
Depends upon why the textbox shows a blank. What is the
control
source
of
the textbox?

--

Ken Snell
<MS ACCESS MVP>

How do I make a TextBox in a report show the number
zero
instead
of
a
blank.

Thank you beforehand.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top