Make field in report show zero and not blank

[T. W.]

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

Thank you beforehand.

 

[Ken Snell [MVP]]

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

 

[T. W.]

I have 8 TextBoxes that pull from a query.

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

 

[Ken Snell [MVP]]

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)

 

[John Spencer (MVP)]

OR use the Format property of the control to force 0 to display when the value
is a null.

Format: #,##0;-#,##0;0;0

That will print zero for nulls. If you wanted decimals then you can add them in.

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.

 

[T. W.]

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).

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.

 

[T. W.]

I tried the below (copy and pasted #,##0;-#,##0;0;0 into the Format field of
the TextBox's property). It produces #Error.

OR use the Format property of the control to force 0 to display when the
value
is a null.

Format: #,##0;-#,##0;0;0

That will print zero for nulls. If you wanted decimals then you can add
them in.

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.



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.

 

[Ken Snell [MVP]]

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).

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.



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. W.]

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);

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).

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.



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.

 

[Ken Snell [MVP]]

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);

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).

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.



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. W.]

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);

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).

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.



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.

 

[Ken Snell [MVP]]

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);


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).

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.



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. W.]

There are 8 text boxes pulling from one query (the query listed in number 3
in the below list).
The control sources match 8 of the ten fields in the query.

I want to be able to have a sum total of 4 (4 count fields, 4 hours fields)
of the values in one record showing with the record. Whether I try it in
the query or the report, it won't show a total at all when the field is
blank. I want a total (per record) of the hours and the count.

 

[T. W.]

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);


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).

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.



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.

 

[Ken Snell [MVP]]

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);


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).

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.



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. W.]

I'll have to try that one to see which is better.

I put the Nz function in the query (in number 3) so it would produce zeros.
In the report I wrapped the control source in each text box with the Val
function as well as the text box that's adding the other text boxes
together. That works well, but can you see any side effects from that; in
other words I assume the Val function doesn't actually make it a number huh?
I'm going to try it your way and see what results I get.

What would be nice, is being able to put things in Cross Tab queries. The
Cross Tab query seems to have a lot of constraints to it.

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);


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.



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. W.]

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);


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.



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.

 

[Ken Snell [MVP]]

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).


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);


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.



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.

 

[Ken Snell [MVP]]

Duane Hookom, ACCESS MVP, is kind of our resident guru on crosstab queries.

I recommend that you post a question in the microsoft.public.access.queries
newsgroup about crosstab queries. Put Crosstab query and Duane's name in the
subject line to get his attention.

--

Ken Snell
<MS ACCESS MVP>

I'll have to try that one to see which is better.

I put the Nz function in the query (in number 3) so it would produce zeros.
In the report I wrapped the control source in each text box with the Val
function as well as the text box that's adding the other text boxes
together. That works well, but can you see any side effects from that; in
other words I assume the Val function doesn't actually make it a number huh?
I'm going to try it your way and see what results I get.

What would be nice, is being able to put things in Cross Tab queries. The
Cross Tab query seems to have a lot of constraints to it.

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);


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.



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. W.]

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?

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).


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.



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.