Show Blank Field Instead of a Zero

[Guest]

Hello,

I have a report that is made up of a summation of several tables, each with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So, for
example, if I have two tables, one labeled "ABC" and one labeled "CDE", they
would each share column "C".

So my report simply queries this table and outputs the results to a report
with column headers:
A B C D E

The problem is that for those lines that have results from table "ABC", I
get zeros where blanks should be in columns "D E" and the same is true for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my desk
looking for a revelation.

Thanks for your time.
Lance

 

[Duane Hookom]

Simply set the Format property of the text boxes to display what you want.
Check Help for "Format Numbers".

 

[Guest]

Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers wrapped in
brackets, and displayed a space if null was encountered. Unfortunately it is
not displaying blanks for all fields that should be. I double checked all of
my tables for default values and there isn't one (I removed the zero and left
it as null).

Any suggestions? Thanks again.

Lance

Simply set the Format property of the text boxes to display what you want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP

Hello,

I have a report that is made up of a summation of several tables, each
with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So, for
example, if I have two tables, one labeled "ABC" and one labeled "CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to a report
with column headers:
A B C D E

The problem is that for those lines that have results from table "ABC", I
get zeros where blanks should be in columns "D E" and the same is true for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my desk
looking for a revelation.

Thanks for your time.
Lance

 

[Duane Hookom]

Is your value the result of a calculation? Could the value be a very small
number that only looks like 0?

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers wrapped in
brackets, and displayed a space if null was encountered. Unfortunately it
is
not displaying blanks for all fields that should be. I double checked all
of
my tables for default values and there isn't one (I removed the zero and
left
it as null).

Any suggestions? Thanks again.

Lance

Simply set the Format property of the text boxes to display what you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP

Hello,

I have a report that is made up of a summation of several tables, each
with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So, for
example, if I have two tables, one labeled "ABC" and one labeled "CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table "ABC",
I
get zeros where blanks should be in columns "D E" and the same is true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my desk
looking for a revelation.

Thanks for your time.
Lance

 

[Guest]

No, the fields are all simple integer data types that are the result of data
entry. The reason it is such a big deal to figure out a way around the zero
default (even though the column is set to a blank for a default value) is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of displaying it
or something.

Thanks for all of your help Duane.

Lance

Is your value the result of a calculation? Could the value be a very small
number that only looks like 0?

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers wrapped in
brackets, and displayed a space if null was encountered. Unfortunately it
is
not displaying blanks for all fields that should be. I double checked all
of
my tables for default values and there isn't one (I removed the zero and
left
it as null).

Any suggestions? Thanks again.

Lance

Simply set the Format property of the text boxes to display what you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several tables, each
with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So, for
example, if I have two tables, one labeled "ABC" and one labeled "CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table "ABC",
I
get zeros where blanks should be in columns "D E" and the same is true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my desk
looking for a revelation.

Thanks for your time.
Lance

 

[Duane Hookom]

Try a format property of

0;(0);"";""

--
Duane Hookom
MS Access MVP
--

No, the fields are all simple integer data types that are the result of
data
entry. The reason it is such a big deal to figure out a way around the
zero
default (even though the column is set to a blank for a default value) is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of displaying it
or something.

Thanks for all of your help Duane.

Lance

Is your value the result of a calculation? Could the value be a very
small
number that only looks like 0?

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers wrapped
in
brackets, and displayed a space if null was encountered. Unfortunately
it
is
not displaying blanks for all fields that should be. I double checked
all
of
my tables for default values and there isn't one (I removed the zero
and
left
it as null).

Any suggestions? Thanks again.

Lance



:

Simply set the Format property of the text boxes to display what you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several tables,
each
with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So,
for
example, if I have two tables, one labeled "ABC" and one labeled
"CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table
"ABC",
I
get zeros where blanks should be in columns "D E" and the same is
true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my
desk
looking for a revelation.

Thanks for your time.
Lance

 

[Guest]

That's getting me a lot closer Duane!!

I just need to get it to leave those zeros that are actually supposed to be
in the fields (where it is a valid entry). It clears all of the zeros right
now but at least it is narrowing the possibilities down.

Thanks a lot for posting that format, I really appreciate you sticking at it.

Lance

Try a format property of

0;(0);"";""

--
Duane Hookom
MS Access MVP
--

No, the fields are all simple integer data types that are the result of
data
entry. The reason it is such a big deal to figure out a way around the
zero
default (even though the column is set to a blank for a default value) is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of displaying it
or something.

Thanks for all of your help Duane.

Lance

Is your value the result of a calculation? Could the value be a very
small
number that only looks like 0?

--
Duane Hookom
MS Access MVP


Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers wrapped
in
brackets, and displayed a space if null was encountered. Unfortunately
it
is
not displaying blanks for all fields that should be. I double checked
all
of
my tables for default values and there isn't one (I removed the zero
and
left
it as null).

Any suggestions? Thanks again.

Lance



:

Simply set the Format property of the text boxes to display what you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several tables,
each
with
one or two matching fields. The result is a summary table with the
culmination of all the fields in each of the individual tables. So,
for
example, if I have two tables, one labeled "ABC" and one labeled
"CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table
"ABC",
I
get zeros where blanks should be in columns "D E" and the same is
true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would greatly
appreciate it so I can stop ramming my face into the corner of my
desk
looking for a revelation.

Thanks for your time.
Lance

 

[Duane Hookom]

I'm lost as to how you would determine which zeros are supposed to be there
and which zeros are not.

--
Duane Hookom
MS Access MVP
--

That's getting me a lot closer Duane!!

I just need to get it to leave those zeros that are actually supposed to
be
in the fields (where it is a valid entry). It clears all of the zeros
right
now but at least it is narrowing the possibilities down.

Thanks a lot for posting that format, I really appreciate you sticking at
it.

Lance

Try a format property of

0;(0);"";""

--
Duane Hookom
MS Access MVP
--

No, the fields are all simple integer data types that are the result of
data
entry. The reason it is such a big deal to figure out a way around the
zero
default (even though the column is set to a blank for a default value)
is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of displaying
it
or something.

Thanks for all of your help Duane.

Lance


:

Is your value the result of a calculation? Could the value be a very
small
number that only looks like 0?

--
Duane Hookom
MS Access MVP


Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers
wrapped
in
brackets, and displayed a space if null was encountered.
Unfortunately
it
is
not displaying blanks for all fields that should be. I double
checked
all
of
my tables for default values and there isn't one (I removed the zero
and
left
it as null).

Any suggestions? Thanks again.

Lance



:

Simply set the Format property of the text boxes to display what
you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several tables,
each
with
one or two matching fields. The result is a summary table with
the
culmination of all the fields in each of the individual tables.
So,
for
example, if I have two tables, one labeled "ABC" and one labeled
"CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to
a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table
"ABC",
I
get zeros where blanks should be in columns "D E" and the same is
true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would
greatly
appreciate it so I can stop ramming my face into the corner of my
desk
looking for a revelation.

Thanks for your time.
Lance

 

[Guest]

When I created my tables, those fields are integers which I have removed the
default value of zero from. That way if they enter a zero, I know it is a
valid entry (plus I ask them to confirm since it is a significant event).

If you look at the data in the tables, it looks correct. There are values
of zero, blanks and regular integer values. It is when it is in a report,
and combined with a couple of other tables where they have overlaps that it
seems to just slap a zero in for no reason. At first I thought it may be
just those fields where the row was from a table that didn't share a common
field, but it didn't seem to be that.

I have never ran across this before, but this is the first time where I have
had a report where I mixed tables with one or two common fields in a report
(i.e. Table One has columns ABC and Table Two has columns CDE).

Report columns would be:
A B C D E

Shared column would be C and the 'invalid' zeros that should be blanks would
appear in columns D or E (no pattern that I can see) if the row was from
table ABC and the reverse would hold true if the columns were from table CDE.

Really strange.

Lance

I'm lost as to how you would determine which zeros are supposed to be there
and which zeros are not.

--
Duane Hookom
MS Access MVP
--

That's getting me a lot closer Duane!!

I just need to get it to leave those zeros that are actually supposed to
be
in the fields (where it is a valid entry). It clears all of the zeros
right
now but at least it is narrowing the possibilities down.

Thanks a lot for posting that format, I really appreciate you sticking at
it.

Lance

Try a format property of

0;(0);"";""

--
Duane Hookom
MS Access MVP
--

No, the fields are all simple integer data types that are the result of
data
entry. The reason it is such a big deal to figure out a way around the
zero
default (even though the column is set to a blank for a default value)
is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of displaying
it
or something.

Thanks for all of your help Duane.

Lance


:

Is your value the result of a calculation? Could the value be a very
small
number that only looks like 0?

--
Duane Hookom
MS Access MVP


Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers
wrapped
in
brackets, and displayed a space if null was encountered.
Unfortunately
it
is
not displaying blanks for all fields that should be. I double
checked
all
of
my tables for default values and there isn't one (I removed the zero
and
left
it as null).

Any suggestions? Thanks again.

Lance



:

Simply set the Format property of the text boxes to display what
you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several tables,
each
with
one or two matching fields. The result is a summary table with
the
culmination of all the fields in each of the individual tables.
So,
for
example, if I have two tables, one labeled "ABC" and one labeled
"CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results to
a
report
with column headers:
A B C D E

The problem is that for those lines that have results from table
"ABC",
I
get zeros where blanks should be in columns "D E" and the same is
true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would
greatly
appreciate it so I can stop ramming my face into the corner of my
desk
looking for a revelation.

Thanks for your time.
Lance

 

[Duane Hookom]

Show us a couple sample records from each table and then the SQL of the
query that pulls them together.

--
Duane Hookom
MS Access MVP
--

When I created my tables, those fields are integers which I have removed
the
default value of zero from. That way if they enter a zero, I know it is a
valid entry (plus I ask them to confirm since it is a significant event).

If you look at the data in the tables, it looks correct. There are values
of zero, blanks and regular integer values. It is when it is in a report,
and combined with a couple of other tables where they have overlaps that
it
seems to just slap a zero in for no reason. At first I thought it may be
just those fields where the row was from a table that didn't share a
common
field, but it didn't seem to be that.

I have never ran across this before, but this is the first time where I
have
had a report where I mixed tables with one or two common fields in a
report
(i.e. Table One has columns ABC and Table Two has columns CDE).

Report columns would be:
A B C D E

Shared column would be C and the 'invalid' zeros that should be blanks
would
appear in columns D or E (no pattern that I can see) if the row was from
table ABC and the reverse would hold true if the columns were from table
CDE.

Really strange.

Lance

I'm lost as to how you would determine which zeros are supposed to be
there
and which zeros are not.

--
Duane Hookom
MS Access MVP
--

That's getting me a lot closer Duane!!

I just need to get it to leave those zeros that are actually supposed
to
be
in the fields (where it is a valid entry). It clears all of the zeros
right
now but at least it is narrowing the possibilities down.

Thanks a lot for posting that format, I really appreciate you sticking
at
it.

Lance

:

Try a format property of

0;(0);"";""

--
Duane Hookom
MS Access MVP
--

No, the fields are all simple integer data types that are the result
of
data
entry. The reason it is such a big deal to figure out a way around
the
zero
default (even though the column is set to a blank for a default
value)
is
that "Zero" is a valid entry and so is a blank field.

I guess I will have to try and figure out a different way of
displaying
it
or something.

Thanks for all of your help Duane.

Lance


:

Is your value the result of a calculation? Could the value be a
very
small
number that only looks like 0?

--
Duane Hookom
MS Access MVP


Hi Duane,

Thanks for the reply; I think I may be on the right track.

I used a Format of:

0;(0);;""

Which should have given me positive numbers, negative numbers
wrapped
in
brackets, and displayed a space if null was encountered.
Unfortunately
it
is
not displaying blanks for all fields that should be. I double
checked
all
of
my tables for default values and there isn't one (I removed the
zero
and
left
it as null).

Any suggestions? Thanks again.

Lance



:

Simply set the Format property of the text boxes to display what
you
want.
Check Help for "Format Numbers".

--
Duane Hookom
MS Access MVP


Hello,

I have a report that is made up of a summation of several
tables,
each
with
one or two matching fields. The result is a summary table
with
the
culmination of all the fields in each of the individual
tables.
So,
for
example, if I have two tables, one labeled "ABC" and one
labeled
"CDE",
they
would each share column "C".

So my report simply queries this table and outputs the results
to
a
report
with column headers:
A B C D E

The problem is that for those lines that have results from
table
"ABC",
I
get zeros where blanks should be in columns "D E" and the same
is
true
for
table "CDE" since columns "A B" should be blank, not zero.

Instead of getting:

A B C D E
======
2 4
9 7
5 1

I am getting:
A B C D E
======
2 4 0 0
0 0 9 7
5 1 0 0

I have tried using Nz(, " " ) and IIF(<>"", , " ")

But neither work for me. If anyone has any advice, I would
greatly
appreciate it so I can stop ramming my face into the corner of
my
desk
looking for a revelation.

Thanks for your time.
Lance