DAO How to set a numeric field to null

[Unscruffed]

I have a db project using textboxes etc bound using a binding collection and custom class.

I need to be able to distinguish between a zero being entered in a numeric field, or the field being left blank, thus the need to be able to set the field to the default value of null. Everything I try gives errors.

Any help appreciated.


Heaven doesn't want me, and Hell's afraid I'll take over!

 

[BobRodes]

Well, you haven't told us what you've tried. What have you tried? Can you post the relevant code, where you try to set your db to a null value?

One other thing. Are you sure that your underlying db will allow you to set the field to null? Perhaps it isn't allowed.

 

[SBerthold]

1. Use a property to change the input to a NULL if blank, and then update the field

or

2. Use the StdDataFormat object (reference to the "Microsoft Data Formating Object Library")

 

[keenanbr]

Perhaps the column is not nullable.

 

[tedsmith]

This function always returns a zero when the field is a null

Function NoNull(InValue as Variant)
NoNull=IIF(IsNull(InValue),0,InValue)
end function

Then when I am interrogating any field in the database I say
MyValue=NoNull(Myrec.Fields(FieldNumber))
or
AmountOfBricks=NoNull(MyRec!NumberOfBricks)

Can also be used for string fields using "" instead of 0