A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old August 17th, 2005, 11:57 PM
Tom
external usenet poster
 
Posts: n/a
Default 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  
Old August 18th, 2005, 12:25 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old August 18th, 2005, 12:59 AM
Tom
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 07: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


All times are GMT +1. The time now is 06:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.