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)

 

[Pat Hartman]

Some fields are required and quantity should be one of them. If 0 does not make sense as a value, the default should be set to null and required should be set to True. That will make the database engine enforce the business rule and you will not need to have to make any accomodation when you use quantity in a calculation.

To give the user a more friendly error message, add code in the FORM's BeforeUpdate event to check the value in quantity and cancel the update if .

If Me.Quantity > 0 Then
Else
    Msgbox "Quantity must be entered and be > 0.",vbOKOnly
    Cancel = True
    Exit Sub
End If

If you want to give a more specific error, then you would check for null, ZLS, 0, and negative values separately. The code is written with a positive If because
If Me.Quantity Not > 0 will not return the desired result if Quantity is null. No value when compared to a null value will ever return true so when Quantity is null, Me.Quantity Not > 0 will return null and NOT true as you might expect.