blank field in calculation

[rainbows]

this is what i use in my update query which i thought would be ok . the field in the table defaults the onorderqty defaults to 0 . but today looking though the records i noticed that we have 5 parts on order yet the on order qty was blank , is seems someone has deleted the 0 and left it blank so the sum dont work how can this be modified so that if some did delete the O the maths still work

[Stocklist]![OnOrderQty]+[Supplier Orders]![qty reqr]

thanks steve

[

 

[GaP42]

Perhaps you could do a check If not isnull(onorderqty) then msg, cancel, set focus to an appropriate control, exit beforeupdate. in the form beforeupdate event? When the form with the onorderqty is updated a null value will not be permitted. You might need this to be presented (not visible is OK) on the parent form if that is where editing is done (and using the name for its control)

 

[Cotswold]

If someone has deleted the value then the field content is a Null. You cannot use Nulls in calcs so you should always prevent it. Your program, you should be in control of all input and output. If your users can mess up they will, rest assured on that. If the calc had been a ÷ or a x then your program would fall over.

Write a Function to trip in the BeforeUpdate() to check that the contents are the correct number type and value. If the user has entered an incorrect value, or a Null then convert their entry to Zero.

Something like ; ValidNumberSingle2Dec(VarPassed as Variant) as Single ; or ValidNumberInteger(VarPassed as Variant) as Long .....etc . Your Function will then check the VarPassed using Vartype() and correct if invalid before returning the value. If the user should not enter a Zero, then check for that and prevent them moving on until corrected. Or some other operation as you see fit, error message etc.

I have dozens of standard Functions in modules to validate all different field types, sizes for any format I may require.

 

[ebs17]

Nz(A, 0) + Nz(B, 0)