Can "Null" values be inserted into a table?

M

Mark

When the following VBA Code is executed I get a run-time error:

strSQL = "Insert Into myTable Values (" & _
Chr$(34) & rst.Fields(0) & Chr$(34) & ", " & _
rst.Fields(1) & ")"

dbs.Execute strSQL

The following run-time error occurs when the strSQL is executed:

Run-time error '3134':

Syntax error in INSERT INTO statement.


I believe the Null value in "rst.Fields(1)" is causing the problem since
when code "Nz(rst.Fields(1), 0)" makes the error go away.

Is there any way to preserve the Null value when Inserting into a table?

Thanks, Mark
 
K

Klatuu

You can't insert a null value and you can only insert a zero length string if
the field properties allow it. Why not just tst for it, and exclude it from
the insert if the value to insert is null?
 
M

Mark

I probably will do what you suggested.

The reason for leaving it as Null is that I'm reading the data from someone
else's database and I really don't want to change the data (in this case to
"0") if I don't have to. Why change it you don't have to? It doesn't feel
right changing a value due to a software limitation.

Thanks for your comments, Mark
 
K

Klatuu

I agree up to the point where you say "software limitation". Null is a very
special beast. In fact, if you want to really confuse yourself, go to the
immediate window and type in:
?Null = Null

Believe it or not, it will return False.
Learning to understand the nature of the beast just takes some practice.
 
D

Douglas J. Steele

You can insert Null values: you simply use the keyword Null (or a variable
that contains a Null value).

The problem is, you can't have quotes around the Null.

Assuming that the first field in myTable is capable of accepting Null
values, the following will work:

strSQL = "Insert Into myTable Values (" & _
IIf(IsNull(rst.Fields(0)), Null, Chr$(34) & rst.Fields(0) & Chr$(34))
& _
", " & rst.Fields(1) & ")"
 
K

Ken Snell [MVP]

I've found that this also will work:

strSQL = "Insert Into myTable Values (" & _
IIf(IsNull(rst.Fields(0)), "Null", Chr$(34) & _
rst.Fields(0) & Chr$(34)) & _
", " & rst.Fields(1) & ")"
 
D

Douglas J. Steele

Actually, you're right: that will work.

(And after looking at it, I'm surprised that mine worked, because I would
have thought that the word Null was explicitly required. However, this was
one of the times when I did explicitly test before posting!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
S

SteveS

Aren't the field names also required?

In help, form2 of the "Insert Into" example shows:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])


so shouldn't strSQL be:

strSQL = "Insert Into myTable ([field1], [field2]) Values (" & _
IIf(IsNull(rst.Fields(0)), "Null", Chr$(34) & _
rst.Fields(0) & Chr$(34)) & _
", " & rst.Fields(1) & ")"


If I substitute "Test" for rst.fields(0) and 1000 for rst.Fields(1) and run
the code, I get the run-time error 3346:

"Number of query Values and Destination fields are not the same"

Am I missing something? (wouldn't be the first time <g>)
 
K

Ken Snell [MVP]

If you want the append query to write data into all the existing fields in
the table, and you provide values for each of those fields, then no you do
not need to specify the target fields.

--

Ken Snell
<MS ACCESS MVP>

SteveS said:
Aren't the field names also required?

In help, form2 of the "Insert Into" example shows:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])


so shouldn't strSQL be:

strSQL = "Insert Into myTable ([field1], [field2]) Values (" & _
IIf(IsNull(rst.Fields(0)), "Null", Chr$(34) & _
rst.Fields(0) & Chr$(34)) & _
", " & rst.Fields(1) & ")"


If I substitute "Test" for rst.fields(0) and 1000 for rst.Fields(1) and
run
the code, I get the run-time error 3346:

"Number of query Values and Destination fields are not the same"

Am I missing something? (wouldn't be the first time <g>)
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Douglas J. Steele said:
Actually, you're right: that will work.

(And after looking at it, I'm surprised that mine worked, because I would
have thought that the word Null was explicitly required. However, this
was
one of the times when I did explicitly test before posting!)
Click to expand...
Click to expand...
 
S

SteveS

So if there are 5 fields in the table and you have 5 values in the "Insert"
statement, the first value is put into the first field in the table, and so
on? But if you only provide 4 values you get an error?

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Ken Snell said:
If you want the append query to write data into all the existing fields in
the table, and you provide values for each of those fields, then no you do
not need to specify the target fields.

--

Ken Snell
<MS ACCESS MVP>

SteveS said:
Aren't the field names also required?

In help, form2 of the "Insert Into" example shows:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])


so shouldn't strSQL be:

strSQL = "Insert Into myTable ([field1], [field2]) Values (" & _
IIf(IsNull(rst.Fields(0)), "Null", Chr$(34) & _
rst.Fields(0) & Chr$(34)) & _
", " & rst.Fields(1) & ")"


If I substitute "Test" for rst.fields(0) and 1000 for rst.Fields(1) and
run
the code, I get the run-time error 3346:

"Number of query Values and Destination fields are not the same"

Am I missing something? (wouldn't be the first time <g>)
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Douglas J. Steele said:
Actually, you're right: that will work.

(And after looking at it, I'm surprised that mine worked, because I would
have thought that the word Null was explicitly required. However, this
was
one of the times when I did explicitly test before posting!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I've found that this also will work:

strSQL = "Insert Into myTable Values (" & _
IIf(IsNull(rst.Fields(0)), "Null", Chr$(34) & _
rst.Fields(0) & Chr$(34)) & _
", " & rst.Fields(1) & ")"

--

Ken Snell
<MS ACCESS MVP>

message
You can insert Null values: you simply use the keyword Null (or a
variable that contains a Null value).

The problem is, you can't have quotes around the Null.

Assuming that the first field in myTable is capable of accepting Null
values, the following will work:

strSQL = "Insert Into myTable Values (" & _
IIf(IsNull(rst.Fields(0)), Null, Chr$(34) & rst.Fields(0) &
Chr$(34)) & _
", " & rst.Fields(1) & ")"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



You can't insert a null value and you can only insert a zero length
string if
the field properties allow it. Why not just tst for it, and exclude
it
from
the insert if the value to insert is null?


:

When the following VBA Code is executed I get a run-time error:

strSQL = "Insert Into myTable Values (" & _
Chr$(34) & rst.Fields(0) & Chr$(34) & ", " & _
rst.Fields(1) & ")"

dbs.Execute strSQL

The following run-time error occurs when the strSQL is executed:

Run-time error '3134':

Syntax error in INSERT INTO statement.


I believe the Null value in "rst.Fields(1)" is causing the problem
since
when code "Nz(rst.Fields(1), 0)" makes the error go away.

Is there any way to preserve the Null value when Inserting into a
table?

Thanks, Mark
Click to expand...
Click to expand...
Click to expand...
 
S

SteveS

I was getting the error because I had 4 fields in the test table; the first
was an autonumber. I wasn't providing a value for it because I assumed that
it would "autonumber " (provide a value) like adding a new record manually.

Once I had values for 4 fields (not just 3 non autonumber fields) no erors
occured.

Learned something new.

Thanks Ken
 
D

Douglas J Steele

Of course, if you've got an AutoNumber field, you shouldn't be supplying it
with a value in your INSERT INTO statement!

Listing the field names explicitly (leaving out the name of the AutoNumber
field), and not passing a value for the AutoNumber field would be the
correct approach.
 
T

Tim Ferguson

You can insert Null values: you simply use the keyword Null (or a
variable that contains a Null value).

The problem is, you can't have quotes around the Null.
Click to expand...

FWIW, I have a set of functions like:

Function SQLDate(SomeDate as Variant) As String
if isnull(somedate) then
sqldate = "null"
elseif isdate(somedate) then
sqldate = format(somedate, JetDateFormat)
else
' or raise an error
sqldate = "Null"
end if
end function

function SQLString(SomeText as Variant) as string
if isnull(Sometext) then
sqlstring = "Null"
else
sqlstring = """" & _
replace(SomeString, """", """""") & _
""""
end if
end function

function SQLNumber(SomeNumber as Variant) as string
' rarely used
If isnull(somenumber) then
sqlNumber = "Null"
Elseif isnumeric(somenumber) then
sqlNumber = Format(SomeNumber,"General") ' cop out!!
Else
sqlNumber = "Null"
' or raise an error
End If
End function



I then call these functions when I build up the SQL string:

With SQLString
.Add "WHERE MyField = " & SQLString(txtName.Value)
.Add " AND MyDate > " & SQLDate(txtBirthDate.Value)

et cetera, et cetera...


Hope that helps


Tim F
 
D

Douglas J. Steele

Tim Ferguson said:
FWIW, I have a set of functions like:

Function SQLDate(SomeDate as Variant) As String
if isnull(somedate) then
sqldate = "null"
elseif isdate(somedate) then
sqldate = format(somedate, JetDateFormat)
else
' or raise an error
sqldate = "Null"
end if
end function

function SQLString(SomeText as Variant) as string
if isnull(Sometext) then
sqlstring = "Null"
else
sqlstring = """" & _
replace(SomeString, """", """""") & _
""""
end if
end function

function SQLNumber(SomeNumber as Variant) as string
' rarely used
If isnull(somenumber) then
sqlNumber = "Null"
Elseif isnumeric(somenumber) then
sqlNumber = Format(SomeNumber,"General") ' cop out!!
Else
sqlNumber = "Null"
' or raise an error
End If
End function



I then call these functions when I build up the SQL string:

With SQLString
.Add "WHERE MyField = " & SQLString(txtName.Value)
.Add " AND MyDate > " & SQLDate(txtBirthDate.Value)

et cetera, et cetera...
Click to expand...

Unfortunately, that can result in "WHERE MyField = Null AND MyDate > Null",
which I don't believe will work.

Great for using to update the values, though.
 
T

Tim Ferguson

Unfortunately, that can result in "WHERE MyField = Null AND MyDate >
Null", which I don't believe will work.
Click to expand...

True enough. This is usually sorted out elsewhere in the program logic --
at least I think I must do since I haven't fallen over this one in
practice!

Good catch, thanks.

Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Suppress display when outputting to a Snapshot file 1
Inserting Boolean values into a table 1
Send an Query output with a Email using "sendObject" function 1
I cannot see the syntax error - guidance please. 2
Shell command produces a "Run-time error '5': Invalid procedure call or argumen 0
Pulling a cell in at the end of a row of data 6
Syntax Error in INSERT INTO Statement 2
Qualifier Must Be Collection 1

Top