Null Value Insert - Syntax Error with Nz()

[polisasimo]

Any help on what is throwing a syntax error here would be greatly appreciated.

In this case the Me.TextBase is Null

SQL = "INSERT INTO SomeTable(Base, Access)" & " VALUES(" & Nz(Me.TextBase, Null) & ", '" & Me.TextAccess & "')"

[June7]

Why bother with Nz since you are using Null as the alternate value anyway? The purpose of Nz is to return a value other than Null.

Is Base a number type field?

Don't see anything wrong with syntax. The first concatenation is unnecessary but shouldn't hurt.

[John_G]

Try putting the Null in quotation marks, because you want to return the string "Null", not an actual Null value:

SQL = "INSERT INTO SomeTable(Base, Access)" & " VALUES(" & Nz(Me.TextBase, "Null") & ", '" & Me.TextAccess & "')"

e.g., you want the SQL to look something like this:

INSERT INTO SomeTable(Base, Access) VALUES( Null, 'Some String')

HTH

John

[June7]

If you want text "Null" instead of actual Null then the code needs apostrophe delimiters:

SQL = "INSERT INTO SomeTable(Base, Access) VALUES('" & Nz(Me.TextBase, "Null") & "', '" & Me.TextAccess & "')"

Then the compiled SQL would be like:

INSERT INTO SomeTable(Base, Access) VALUES('Null', 'Some String')

But if Base is number type field, cannot save text "Null".