How do I insert null into an integer field with VBA?

Question

I have an MS Access ADP with a SQL server backend. I want to take a bunch of values from a form and insert them into a table. Some of the values can be null. How do I use vba to insert null into an integer field in the SQL Server table?

I tried

"insert...values(" & nz(me.myInt, null) & ",..."

and

"insert...values(" & nz(me.myInt, "null") & ",..."

Neither worked.

Answer 1

IF the field will accept nulls then simply leave it out of the list of fields and it will get a null value. So given a table with Name, Address, phone, doing the equivalent of

INSERT table(Name, Address) VALUES('fred','Toytown')

will leave the phone number with a null value

Answer 2

your second example looks good

("insert...values(" & nz(me.myInt, "null") & ",...")

but only if me.myInt is a variant, a control, or a field. If myInt is an integer or a long, then it cannot be null.