Numeric Field when null display text

[vanny]

Hi All,

I have a field called PRICE and obviously it is a NUMERIC field as it need to perform calculations such as calculating Total Quantity * Price etc. At the moment the PRICE is inputted manually by the User on a Form and when it has no Price it is simple left blank.

What I wish to include is that on the REPORT when the field is empty it writes the text FOC instead of leaving it empty.

Can you please anyone suggest a way of doing this.

Thanks any help will be much appreciated

 

[Brianwarnock]

Does this thread suggest a solution?

Brian

 

[Jonathan Kok]

If you multiply the blank value (which Access stores as 'Null') by the quantity, you get...a null value. This is helpful.

Use the 'Nz' function to change the Null value to whatever you want.
Usage:
Nz([fieldname],"<replacement>")

Eg:
=Nz(([price]*[qty]),"FOC")

Note: <replacement> is optional. If left blank, ie, Nz([fieldname]), Nz returns a zero-length field.

 

[vanny]

Thanks for your help. It successfully worked

 

[vanny]

Numeric Field when null display tex

So basically now the basic concept of transforming null fields into FOC text is working. However I still need to configure out the process of summing up the Total Value.

This is because i have a list of Products is the PRICE field is null then it is turned as FOC in the report. However when it comes to summing up the Quantity * Price ( =Sum([PQty]*[PPx])) it is not returning anything as the field is null.

Can anyone suggest a method of what should I include in the field so that it converts null into 0 and performs the formula correclty.

Thanks a lot.

 

[boblarson]

Make sure that your controls that display [PQty] and [PPx] are not named the same as their fields. You then should be able to put your formula (referring to the fields and NOT the controls) as the control source of a text box in the report footer.

 

[RoyVidar]

You can play with formats in Access, as you can in Excel. The format property, accepts four different properties for numbers, separated by semicolon, check out the help file.

So without any functions and stuff, only keep the original control, enter the format property of it, and then for instance something like this:

# ##0.00;# ##0.00;# ##0.00;"FOC"

Or format appropriate for your regional settings. Then sums'n'stuff should work, cause this isn't altering any values, it's just formatting

The different sections are

1 - format for positive numbers
2 - format for negative numbers
3 - format for zero
4 - format if Null

Edit: Oh, forgot the "summing" control,

# ##0.00;# ##0.00;# ##0.00;0

or

# ##0.00;# ##0.00;# ##0.00;0.00

 

[Moniker]

The best way around handling NULLs is to convert them to something useful to you as early as possible. In the textbox where you currently have users entering a price, you have two ways to do this:

1) Default the value of the TextBox to 0. All new records will default to that.

2) On the TextBox control's AfterUpdate, use the Nz function to convert a NULL to a 0. (The literal name of Nz is NullZero, and the usage of the Nz function is detailed throughout this thread and forum)

HTH
~Moniker

 

[RoyVidar]

"The best way around handling NULLs is to convert them to something useful to you as early as possible."

hm - Null isn't useful?

How then differentiate between something which is unknown, vs something having the specific value 0?