blank if zero

[Keith G Hicks]

What setting on a form or a form's control determines whether or not a
textbox shows BLANK if the value is zero? I'm working on something from a
previous programmer and all of the controls on one of the forms show blank
if the value is zero. The controls that I added for some new fields show
zeros. I've compared properties one by one for each of the controls and
don't see any difference.

Any help greatly appreciated.

THanks,

Keith

 

[PC Datasheet]

First, you need to determine if the textbox is bound to a field in the
recordsource of the form. If it is, then it will always show the value in the
field; it CAN NOT show Blank when the value in the field is zero. If it is not
bound to a field in the recordsource and it is showing a different value in each
record, the value you see in the textbox is being set in the default value
property of the textbox, in the controsource property of the textbox or by code
in the form's module. Check these in that order. Via code could be in one of
numerous events related to things in the form so to check for code, open the
form in design view and then click on the code button in the menu at the top to
go to the codse module of the form. Start at the very top of the module and look
for something like:

Me!NameOfTextBox = .................
or
Forms!NameOfForm!NameOfTextBox = .......................

 

[Marshall Barton]

What setting on a form or a form's control determines whether or not a
textbox shows BLANK if the value is zero? I'm working on something from a
previous programmer and all of the controls on one of the forms show blank
if the value is zero. The controls that I added for some new fields show
zeros. I've compared properties one by one for each of the controls and
don't see any difference.

The Format property can do that. Formats can consist of
four parts which will be used if the value is positive,
negative, zero or Null.

Another way is to use an expression like =Nz(field, "")
instead of just bunding the text box directly to the field.

Yet another way is to do it is in the report's record source
query using the Nz function.

I suppose it's also possible to be ridiculously devious and
use Conditional Formatting and set the ForeColor to White
for zero value.

 

[Keith G Hicks]

Thanks for the effort. I guess I wasn't clear enough and perhaps sounded
like a novice programmer. Let me clarify what is going on here. (The
program was originally written in MS Access 2.0 and is now being run (after
consecutive upgrades over the years) in A2k. (Did earlier versions of Access
have such a property that is somehow lingering?)

1. There is no code anywhere in the form that would change the format or
make the text white on a white background. I already checked for that.
2. I've taken an existing control (where 0 is blank), copied it and set it's
source to one of the new fields. The behavior is the same as the older
controls. Zero values are blank.
3. I've compared each and every property of the controls and there are NO
differences except for the name, top and left properties.
4. All the text boxes on the form are bound to fields in the database
5. The default value for the old fiedls and the new fields is 0
6. The data in both new and old fields for older records is never blank
(null). There is always a value in all the data (zero or otherwise).
7. When you click into one of the older controls that is showing BLANK, you
suddenly see a 0. When you click out, the 0 is gone and you see a BLANK
control again
8. There are NO events on the controls at all (no OnExit, OnEnter, GotFocus,
LostFocus or anything else)
9. The format property is set to: #,##0.00[Red];(#,##0.00)[Blue]. There is
no reason of course for that to reslult in a blank control.
10. The source of the form is just a plain old query returning basic data.
I ran the query on it's own and every field has either 0 or non-zero values
(no blanks). No special formatting on the fields returned by the query
because he's just returning TableName.*
11. I have another program that I took over from a previous programmer
several years ago that has the same exact behavior. It was very frustrating
to me at the time (there is no code or formatting that would make it display
like that). The client likes to see blank "cells" in continuous forms
because there are so many of them. To display 0's would be distracting.
When I had to create some new objects for them I ended up having to copy
controls from other forms to get the same behavior. I pretty much gave up
on trying to figure out how to do this and did it the grungy way. I've seen
"Blank if 0" property settings in other languages before. I've never been
able to see this in MS Access. But again , I wonder if it is a carryover
from earlier versions. If so, it's a nice feature and annoying that MS
would have eliminated it. Coding that sort of thing is a pain.

Thanks for any help I might get on this

Keith

 

[Marshall Barton]

Thanks for the effort. I guess I wasn't clear enough and perhaps sounded
like a novice programmer. Let me clarify what is going on here. (The
program was originally written in MS Access 2.0 and is now being run (after
consecutive upgrades over the years) in A2k. (Did earlier versions of Access
have such a property that is somehow lingering?)
[snip]
7. When you click into one of the older controls that is showing BLANK, you
suddenly see a 0. When you click out, the 0 is gone and you see a BLANK
control again [snip]
9. The format property is set to: #,##0.00[Red];(#,##0.00)[Blue]. There is
no reason of course for that to reslult in a blank control. [snip]
11. I have another program that I took over from a previous programmer
several years ago that has the same exact behavior. It was very frustrating
to me at the time (there is no code or formatting that would make it display
like that). The client likes to see blank "cells" in continuous forms
because there are so many of them. To display 0's would be distracting.
When I had to create some new objects for them I ended up having to copy
controls from other forms to get the same behavior. I pretty much gave up
on trying to figure out how to do this and did it the grungy way. I've seen
"Blank if 0" property settings in other languages before. I've never been
able to see this in MS Access. But again , I wonder if it is a carryover
from earlier versions. If so, it's a nice feature and annoying that MS
would have eliminated it. Coding that sort of thing is a pain.

You can get that behavior if you use a format of

#,##0.00[Red];(#,##0.00)[Blue];

Note the extra semi-colon at the end. This indicates there
is no format for zero values (i.e. blank). The totally
misleading thing about it is that Access sees the "extra"
semi-colon as redundant when it "normalizes" the format for
display, but it is still effective. Try setting the
formatting one of the old text boxes without the semi-colon
and a new text box with the semi-colon and see what happens.

 

[Keith G Hicks]

That helps. I noticed just now that there is actually a SPACE after the
semicolon in these older controls. The trouble is taht when I try to copy
the same formatting string into the newer controls it won't take. The
semicolon and the space are immediatly removed by MS Access when I move off
of that property. Same holds true when I try to type them in manually.

Interestingly in the other program I mentioned that I'd been working with
for a few years, the format property is set to #,### with no spaces or
semicolons at all. And the result is similar. 0 values appear blank. I
tried a brand new text box in one of those forms and typed #,### and the
result was as expected, blank when 0.

Thanks for the effort. I guess I wasn't clear enough and perhaps sounded
like a novice programmer. Let me clarify what is going on here. (The
program was originally written in MS Access 2.0 and is now being run (after
consecutive upgrades over the years) in A2k. (Did earlier versions of Access
have such a property that is somehow lingering?)
[snip]
7. When you click into one of the older controls that is showing BLANK, you
suddenly see a 0. When you click out, the 0 is gone and you see a BLANK
control again [snip]
9. The format property is set to: #,##0.00[Red];(#,##0.00)[Blue]. There is
no reason of course for that to reslult in a blank control. [snip]
11. I have another program that I took over from a previous programmer
several years ago that has the same exact behavior. It was very frustrating
to me at the time (there is no code or formatting that would make it display
like that). The client likes to see blank "cells" in continuous forms
because there are so many of them. To display 0's would be distracting.
When I had to create some new objects for them I ended up having to copy
controls from other forms to get the same behavior. I pretty much gave up
on trying to figure out how to do this and did it the grungy way. I've seen
"Blank if 0" property settings in other languages before. I've never been
able to see this in MS Access. But again , I wonder if it is a carryover
from earlier versions. If so, it's a nice feature and annoying that MS
would have eliminated it. Coding that sort of thing is a pain.

You can get that behavior if you use a format of

#,##0.00[Red];(#,##0.00)[Blue];

Note the extra semi-colon at the end. This indicates there
is no format for zero values (i.e. blank). The totally
misleading thing about it is that Access sees the "extra"
semi-colon as redundant when it "normalizes" the format for
display, but it is still effective. Try setting the
formatting one of the old text boxes without the semi-colon
and a new text box with the semi-colon and see what happens.

 

[Marshall Barton]

That helps. I noticed just now that there is actually a SPACE after the
semicolon in these older controls. The trouble is taht when I try to copy
the same formatting string into the newer controls it won't take. The
semicolon and the space are immediatly removed by MS Access when I move off
of that property. Same holds true when I try to type them in manually.

Yes, they are removed from the display, but, as I said
earlier, the missing format string for zero values is still
effective (at least in my A97 and AXP tests), did you try
it?

Personally, I prefer to supress zero values with an explicit
ZLS for the format

#,##0.00;-#,##0.00;""

but it still works the same by leaving off the ""

Interestingly in the other program I mentioned that I'd been working with
for a few years, the format property is set to #,### with no spaces or
semicolons at all. And the result is similar. 0 values appear blank. I
tried a brand new text box in one of those forms and typed #,### and the
result was as expected, blank when 0.

Well, I hadn't thought of that format, but yes of course
it's blank for zero. That's what # does.
--
Marsh
MVP [MS Access]

Thanks for the effort. I guess I wasn't clear enough and perhaps sounded
like a novice programmer. Let me clarify what is going on here. (The
program was originally written in MS Access 2.0 and is now being run (after
consecutive upgrades over the years) in A2k. (Did earlier versions of Access
have such a property that is somehow lingering?)
[snip]
7. When you click into one of the older controls that is showing BLANK, you
suddenly see a 0. When you click out, the 0 is gone and you see a BLANK
control again [snip]
9. The format property is set to: #,##0.00[Red];(#,##0.00)[Blue]. There is
no reason of course for that to reslult in a blank control. [snip]
11. I have another program that I took over from a previous programmer
several years ago that has the same exact behavior. It was very frustrating
to me at the time (there is no code or formatting that would make it display
like that). The client likes to see blank "cells" in continuous forms
because there are so many of them. To display 0's would be distracting.
When I had to create some new objects for them I ended up having to copy
controls from other forms to get the same behavior. I pretty much gave up
on trying to figure out how to do this and did it the grungy way. I've seen
"Blank if 0" property settings in other languages before. I've never been
able to see this in MS Access. But again , I wonder if it is a carryover
from earlier versions. If so, it's a nice feature and annoying that MS
would have eliminated it. Coding that sort of thing is a pain.

You can get that behavior if you use a format of

#,##0.00[Red];(#,##0.00)[Blue];

Note the extra semi-colon at the end. This indicates there
is no format for zero values (i.e. blank). The totally
misleading thing about it is that Access sees the "extra"
semi-colon as redundant when it "normalizes" the format for
display, but it is still effective. Try setting the
formatting one of the old text boxes without the semi-colon
and a new text box with the semi-colon and see what happens.

 

[Keith G Hicks]

Yes, they are removed from the display, but, as I said
earlier, the missing format string for zero values is still
effective (at least in my A97 and AXP tests), did you try
it?

Yes I tried running the form after Access removed the semicolon adn the
space from the format. It still showed a zero instead of blank. I'd prefer
#,##0.00[Red];(#,##0.00)[Blue];"" also. It's much clearer and it doesn't go
away on you. Thanks for all the info on this. I appreciate it. - Keith

Personally, I prefer to supress zero values with an explicit
ZLS for the format

#,##0.00;-#,##0.00;""

but it still works the same by leaving off the ""

Interestingly in the other program I mentioned that I'd been working with
for a few years, the format property is set to #,### with no spaces or
semicolons at all. And the result is similar. 0 values appear blank. I
tried a brand new text box in one of those forms and typed #,### and the
result was as expected, blank when 0.

Well, I hadn't thought of that format, but yes of course
it's blank for zero. That's what # does.
--
Marsh
MVP [MS Access]

Keith G Hicks wrote:

Thanks for the effort. I guess I wasn't clear enough and perhaps sounded
like a novice programmer. Let me clarify what is going on here. (The
program was originally written in MS Access 2.0 and is now being run (after
consecutive upgrades over the years) in A2k. (Did earlier versions of Access
have such a property that is somehow lingering?)

[snip]
7. When you click into one of the older controls that is showing

BLANK,
you

suddenly see a 0. When you click out, the 0 is gone and you see a BLANK
control again
[snip]
9. The format property is set to: #,##0.00[Red];(#,##0.00)[Blue].

There
is

no reason of course for that to reslult in a blank control.
[snip]
11. I have another program that I took over from a previous programmer
several years ago that has the same exact behavior. It was very frustrating
to me at the time (there is no code or formatting that would make it display
like that). The client likes to see blank "cells" in continuous forms
because there are so many of them. To display 0's would be distracting.
When I had to create some new objects for them I ended up having to copy
controls from other forms to get the same behavior. I pretty much

gave
up

on trying to figure out how to do this and did it the grungy way.

I've
seen

"Blank if 0" property settings in other languages before. I've never been
able to see this in MS Access. But again , I wonder if it is a carryover
from earlier versions. If so, it's a nice feature and annoying that MS
would have eliminated it. Coding that sort of thing is a pain.

You can get that behavior if you use a format of

#,##0.00[Red];(#,##0.00)[Blue];

Note the extra semi-colon at the end. This indicates there
is no format for zero values (i.e. blank). The totally
misleading thing about it is that Access sees the "extra"
semi-colon as redundant when it "normalizes" the format for
display, but it is still effective. Try setting the
formatting one of the old text boxes without the semi-colon
and a new text box with the semi-colon and see what happens.

 

[Marshall Barton]

Yes I tried running the form after Access removed the semicolon adn the
space from the format. It still showed a zero instead of blank. I'd prefer
#,##0.00[Red];(#,##0.00)[Blue];"" also. It's much clearer and it doesn't go
away on you. Thanks for all the info on this. I appreciate it. - Keith

Hmmmm, I wonder what difference between our tests produces
different results. You're using A2k and I tried it in A97
and AXP so I suppose it could be the different versions, but
I doubt it. If not, then what is it? Oh well, since that
was pretty much your original question, I guess we don't
really have any new information to explain it. I'll try to
keep it in mind in case I stumble over this issue again.