SQL and Null

D

DS

I need to be able to Insert the TxtMemo field if it is null, right now I
have it like this...

"'" & Forms!frmStoreCreditAdd!TxtMemo & "'," & _
Which I have a feeling it isn't creating a Null Record..

When I try this...
"" & Forms!frmStoreCreditAdd!TxtMemo & "," & _
I get an Error if it is null...so how do I insert this record if it is null?

Any help appreciated.
Thanks
DS



Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDate,
CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"
 
B

Bob Hairgrove

I need to be able to Insert the TxtMemo field if it is null, right now I
have it like this...

"'" & Forms!frmStoreCreditAdd!TxtMemo & "'," & _
Which I have a feeling it isn't creating a Null Record..
Click to expand...

You mean it's not inserting a null value in that column: correct, it
is inserting an empty string, which isn't the same thing.
When I try this...
"" & Forms!frmStoreCreditAdd!TxtMemo & "," & _
I get an Error if it is null...so how do I insert this record if it is null?
Click to expand...

You need this:

IIf(IsNull(Forms!frmStoreCreditAdd!TxtMemo), _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'", _
"Null") & "," _

Or, as an alternative solution, you could just leave that column out
of the SQL entirely.
 
D

DS

Bob said:
You mean it's not inserting a null value in that column: correct, it
is inserting an empty string, which isn't the same thing.




You need this:

IIf(IsNull(Forms!frmStoreCreditAdd!TxtMemo), _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'", _
"Null") & "," _

Or, as an alternative solution, you could just leave that column out
of the SQL entirely.
Click to expand...
Thanks, So in passing you say that it's an empty string, how do you code
that...
iiF(Me.Txt= ?EmptyString?, "","M")
Thanks
DS
 
B

Bob Hairgrove

Thanks, So in passing you say that it's an empty string, how do you code
that...
iiF(Me.Txt= ?EmptyString?, "","M")
Click to expand...

I usually just look at the value returned by the Len() function. If
it's 0, it is an empty string. But a blank form control will be null.
Which one do you really want?
 
D

DS

Bob said:
I usually just look at the value returned by the Len() function. If
it's 0, it is an empty string. But a blank form control will be null.
Which one do you really want?
Click to expand...
Well i'm using the Null, but I would like to have the knowledge on how
to do empty strings as well. Thank you for your input.
DS
 
A

Anthos

I apologise if I am repeating what has already been stated
You can't use null in this section because you are putting quotation
marks around it in your SQL query, and as Null is neither an emptry
string, nor an actual value, then it will error out.

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"



The iif() statement that was mentioned before is the preffered method,
However, if you are using Null, you can preseat the SQL query with
single quotation marks.

Null

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"" & Iif(IsNull(Forms!frmStoreCreditAdd!TxtMemo) = true, vbNull, "'" &
Forms!frmStoreCreditAdd!TxtMemo & "'") & "," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"

Empty String

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"" & Iif(IsNull(Forms!frmStoreCreditAdd!TxtMemo) = true, "'" & "'", "'"
& Forms!frmStoreCreditAdd!TxtMemo & "'") & "," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"


Please let me know if I have lead you up the garden path, or I am not
answering your question as required.

Regards
Anthony
 
D

Douglas J. Steele

This will handle both Nulls and empty strings, setting both of them to Null

IIf(Len(Forms!frmStoreCreditAdd!TxtMemo & "") > 0, _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'", _
"Null") & "," _
 
D

DS

Douglas said:
This will handle both Nulls and empty strings, setting both of them to Null

IIf(Len(Forms!frmStoreCreditAdd!TxtMemo & "") > 0, _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'", _
"Null") & "," _
Click to expand...
Great! This is perfect!
Thanks
DS
 
D

DS

Anthos said:
I apologise if I am repeating what has already been stated
You can't use null in this section because you are putting quotation
marks around it in your SQL query, and as Null is neither an emptry
string, nor an actual value, then it will error out.

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"



The iif() statement that was mentioned before is the preffered method,
However, if you are using Null, you can preseat the SQL query with
single quotation marks.

Null

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"" & Iif(IsNull(Forms!frmStoreCreditAdd!TxtMemo) = true, vbNull, "'" &
Forms!frmStoreCreditAdd!TxtMemo & "'") & "," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"

Empty String

Me.TxtCreditID = Nz(DMax("[CreditID]", "tblStoreCredit"), 0) + 1
CurrentDb.Execute "INSERT Into
tblStoreCredit(CreditID,CreditCustomerID,CreditAmount,CreditReason,CreditDa­te,

CreditMemo,CreditIssuerID,CreditManagerID) " & _
"VALUES('" & Forms!frmStoreCreditAdd!TxtCreditID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCustomerID & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditAmount & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtCreditReason & "'," & _
"#" & Forms!frmStoreCreditAdd!TxtCreditDate & "#," & _
"" & Iif(IsNull(Forms!frmStoreCreditAdd!TxtMemo) = true, "'" & "'", "'"
& Forms!frmStoreCreditAdd!TxtMemo & "'") & "," & _
"'" & Forms!frmStoreCreditAdd!TxtEmployee & "'," & _
"'" & Forms!frmStoreCreditAdd!TxtManager & "')"


Please let me know if I have lead you up the garden path, or I am not
answering your question as required.

Regards
Anthony
Click to expand...
Thank you both of these solutions work wel!
DS
 
B

Bob Hairgrove

You need this:

IIf(IsNull(Forms!frmStoreCreditAdd!TxtMemo), _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "'", _
"Null") & "," _
Click to expand...

Oops ... I'm sorry, I have the order wrong here! It should be like
this:

IIf(IsNull(Forms!frmStoreCreditAdd!TxtMemo), _
"Null,", _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "',") _

(Surprised that no one caught this ... )

BTW, to use Null in an SQL query string, you need the string "Null"
(without quotes) and not vbNull, as someone else suggested. IOW, the
SQL should look similar to this:

"INSERT INTO MyTable (LastName, FirstName, Address, City, State, Zip)
VALUES ('Smith', 'John', Null, Null, Null, Null);"
 
D

DS

Bob said:
Oops ... I'm sorry, I have the order wrong here! It should be like
this:

IIf(IsNull(Forms!frmStoreCreditAdd!TxtMemo), _
"Null,", _
"'" & Forms!frmStoreCreditAdd!TxtMemo & "',") _

(Surprised that no one caught this ... )

BTW, to use Null in an SQL query string, you need the string "Null"
(without quotes) and not vbNull, as someone else suggested. IOW, the
SQL should look similar to this:

"INSERT INTO MyTable (LastName, FirstName, Address, City, State, Zip)
VALUES ('Smith', 'John', Null, Null, Null, Null);"
Click to expand...
Thanks, I'll keep a copy of this sinc Ido alot with nulls!
DS
 

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

SQL HANGS 1
Parameters And Null Values 2
sql to pass value to variable to pass to text box 4
IIF ([date_combo_box] Is Null,“xâ€, Between date 1 And date 2) 1
Search and insert into continuous subform 3
criteria and null values 1
SQL IN Clause and Null Value 1
Invalid Use of Null In form and module 0

Top