Design Table to show "Null" or "0" for Blank fields

[KrispyData]

Is there a way to set the design of a table to show Null for blank text
fields or a zero for blank number fields? I guess I could use an update
query? But then I would have to run it everytime I add new records, right?

 

[John Spencer MVP]

Do you want to DISPLAY Null or 0 or do you want to automatically STORE "Null"
in text fields and 0 in number fields?

Automatic storage could be accomplished by setting the default values of the
fields. That would not prevent someone from deleting what was in the field
when entering data.

If you want to display the word "Null" or the value zero and leave the stored
value as null, you can use the format property. Format property for text
fields would be
@;"Null"
Format property for integer numbers might be
#;-#;0;0
Format property for double, single, decimal numbers will be more complex
unless you specify the number of decimal places to show.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KrispyData]

I wanted to display the null or 0. This is Perfect! Thank you, John.

Just to be sure I understand this correctly: If I choose to STORE the null
or 0, then this would affect my query critieria, right? For example, if I
wanted to filter on 0 sales. I cannot put "Is Null" in my criteria.
Instead, I would have to put "Is 0". Is this right?

 

[John Spencer MVP]

If you stored the value (not a good idea) then you would need criteria of
= 0

or

= "Null"



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KrispyData]

Thank you, John.
Have a great weekend.