If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Invalid use of Null" (INSERT statement); can I bypass via "IF
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 ========================== |
#2
|
|||
|
|||
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 -- Ken Snell MS ACCESS MVP "Tom" wrote in message ... 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 ========================== |
#3
|
|||
|
|||
Thanks, Ken, that works great.
-- Thanks, Tom "Ken Snell [MVP]" wrote in message ... 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 -- Ken Snell MS ACCESS MVP "Tom" wrote in message ... 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 ========================== |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
"Invalid SQL Statement" message from union query | Alison Downing via AccessMonster.com | Running & Setting Up Queries | 2 | April 14th, 2005 10:30 AM |
Challenging Charting | C TO | Charts and Charting | 0 | January 17th, 2005 06:57 PM |
Values not equal but not returning in Query | Lynn Arlington | Running & Setting Up Queries | 7 | October 1st, 2004 06:23 PM |
UNION Query with Criteria | Dkline | Running & Setting Up Queries | 1 | August 4th, 2004 09:15 PM |
"UNION" Query with different fields in the two tables | Dkline | Running & Setting Up Queries | 5 | July 30th, 2004 09:05 PM |