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