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.

Solution 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

Solution 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.

Solution 3

what error are you getting when you run your code?

say we have table... [test] with columns [id], [test] and [test 2].

you could write;

insert into [test] ([test], [test 2]) values ( null, null)

make sure that your string that you are passing the command in on shows the null w/o any kind of decoration... no single or double quotes.