Invalid use of Null" (INSERT statement); can I bypass via "IF

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I use the strSQL command below to insert values into a table. This works
fine except that I must have values in all 4 fields. If I'd wouldn't enter
data into the 2 optional fields, I get the runtime-error 94 ("Invalid use of
Null").

For right now, I placed a default value of "---" into the 2 optional fields.
That allows me to bypass the run-time error; however, I'd rather have no
value at all in the optional fields (if I chose to not enter anything at
all).

My question: Can I modify the INSERT statement below so that entering
values in "cboOptionalField1" and "cboOptionalField2" is truly optional and
I won't get the "Invalid use of Null" error?

Tom

==========================

strSQL = "INSERT INTO Table1 (RequiredField1, RequiredField12, " & _
"OptionalField1, OptionalField2) " & _
"VALUES ('" & cboRequiredField1 & "', '" & cboRequiredField2 & "',
" & _
"'" & cboOptionalField1 & "', '" & cboOptionalField1
& "')"

CurrentDb().Execute strSQL, dbFailOnError

==========================
 
Build the SQL statement based on what's in the two optional combo boxes:

strSQL = "INSERT INTO Table1 (RequiredField1, RequiredField12 "
If Len(cboOptionalField1 & "") > 0 Then _
strSQL = strSQL & ", OptionalField1"
If Len(cboOptionalField2 & "") > 0 Then _
strSQL = strSQL & ", OptionalField2"
strSQL = strSQL & ") VALUES ( '" & cboRequiredField1 & "', '" & _
cboRequiredField2 & "'"
If Len(cboOptionalField1 & "") > 0 Then _
strSQL = strSQL & ", '" & cboOptionalField1 & "'"
If Len(cboOptionalField2 & "") > 0 Then _
strSQL = strSQL & ", '" & cboOptionalField2 & "'"
strSQL = strSQL & ");"

CurrentDb().Execute strSQL, dbFailOnError
 
Thanks, Ken, that works great.

--
Thanks,
Tom


Ken Snell said:
Build the SQL statement based on what's in the two optional combo boxes:

strSQL = "INSERT INTO Table1 (RequiredField1, RequiredField12 "
If Len(cboOptionalField1 & "") > 0 Then _
strSQL = strSQL & ", OptionalField1"
If Len(cboOptionalField2 & "") > 0 Then _
strSQL = strSQL & ", OptionalField2"
strSQL = strSQL & ") VALUES ( '" & cboRequiredField1 & "', '" & _
cboRequiredField2 & "'"
If Len(cboOptionalField1 & "") > 0 Then _
strSQL = strSQL & ", '" & cboOptionalField1 & "'"
If Len(cboOptionalField2 & "") > 0 Then _
strSQL = strSQL & ", '" & cboOptionalField2 & "'"
strSQL = strSQL & ");"

CurrentDb().Execute strSQL, dbFailOnError
Click to expand...
 
Back
Top