Is Null and Insert
Experts,
I have the following code that INSERTS data to a table. It works fine but when the [DATEOFISSUE] is null then I get an error. i need to have a handler for NULL.
How I reproduce the error is if I delete a date in this [DATEOFISSUE]. Sometimes I have to delete the date because the date was entered in error by a user.
(I would also like to know if the setwarnings is not advised)
Private Sub txtDateOfIssueSB_BeforeUpd
Dim strSQL As String
'DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
I have the following code that INSERTS data to a table. It works fine but when the [DATEOFISSUE] is null then I get an error. i need to have a handler for NULL.
How I reproduce the error is if I delete a date in this [DATEOFISSUE]. Sometimes I have to delete the date because the date was entered in error by a user.
(I would also like to know if the setwarnings is not advised)
Private Sub txtDateOfIssueSB_BeforeUpd
Dim strSQL As String
'DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
You will not get a warning when use CurrentDB.Execute - only with CurrentDB.RunSQL.
When using a form to create a SQL statement, you will get an error if you leave a date control blank (null) and the code then tries to wrap the Null with pound signs. Catch the error and in the error code re-create strSQL with:
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, 'm\/d\/yyyy') & "#," & Me!ID & "," & Me!EndUserID & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
When using a form to create a SQL statement, you will get an error if you leave a date control blank (null) and the code then tries to wrap the Null with pound signs. Catch the error and in the error code re-create strSQL with:
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, 'm\/d\/yyyy') & "#," & Me!ID & "," & Me!EndUserID & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If Me!DateOfIssueSB = "" Then
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", ,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", ,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
ASKER
Hi Lee,
I reproduced the error and it does say a syntax error and the debugger highlights the DoCmd.RunSQL strSQL
I think it is a syntax error because I dont have a handler for Null. I believe the error is the way the code is written as it does not allow for a Null.
<Where is the [DATEOFISSUE] in the SQL? Is it this Me!DateOfIssueSB?
It is located in tblLCIssueDateHistory. the Me!DateOfIssueSB is located on tblLetterofCredit.
<Is your underlying table set to disallow Null in this field?
in tblLCIssueDateHistory the corresponding name is ISSUEDATE (DATEOFISSUESB is inserted into this ISSUEDATE in this table LCAMENDHistory.) There is no restriction lilke disallow Nulll. I dont see it. (see screen print)
<What ARE you allowing besides a valid date - empty string "", 0?
I am not sure how to answer this. DateOfIssue does not have any restriction to my knowledge. The code above is the only place I think it woudl say to allow empty string and I dont think there is any language there.
What do you think now?
I reproduced the error and it does say a syntax error and the debugger highlights the DoCmd.RunSQL strSQL
I think it is a syntax error because I dont have a handler for Null. I believe the error is the way the code is written as it does not allow for a Null.
<Where is the [DATEOFISSUE] in the SQL? Is it this Me!DateOfIssueSB?
It is located in tblLCIssueDateHistory. the Me!DateOfIssueSB is located on tblLetterofCredit.
<Is your underlying table set to disallow Null in this field?
in tblLCIssueDateHistory the corresponding name is ISSUEDATE (DATEOFISSUESB is inserted into this ISSUEDATE in this table LCAMENDHistory.) There is no restriction lilke disallow Nulll. I dont see it. (see screen print)
<What ARE you allowing besides a valid date - empty string "", 0?
I am not sure how to answer this. DateOfIssue does not have any restriction to my knowledge. The code above is the only place I think it woudl say to allow empty string and I dont think there is any language there.
What do you think now?
ASKER
Gray: did not see your post.... will read it
ASKER
GreyL; I made the change but I still get the SYNTAX ERROR as posted above.
I made addtl comments and maybe this will change your answer...
I made addtl comments and maybe this will change your answer...
ASKER
the debugger highlights the DoCmd.RunSQL strSQL line
Replace ,, with ,Null, in the first SQL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Grey, please see if below is correct on the first SQL... i am still getting the Syntax but not sure about the commas and quotes are corre ct.
Private Sub txtDateOfIssueSB_BeforeUpd
Dim strSQL As String
'DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
'GreyL 3/26/11:
If Me!DateOfIssueSB = "" Then
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ",Null,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
' strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
Private Sub txtDateOfIssueSB_BeforeUpd
Dim strSQL As String
'DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
'GreyL 3/26/11:
If Me!DateOfIssueSB = "" Then
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ",Null,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
' strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
I see my mistake:
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & Null & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & Null & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
ASKER
Gray: i made the change and damn it still is giving me the error as above. I wonder if wrapping the [DateOfIssueSB] in the Nz would do the trick?
Lets recap: you run the query and when the text box Me!DateOfIssueSB is filled in - no problem. When you return and leave the same box empty, you get an error. Do you get an error if you leave it empty in the first place?
ASKER
GrayL:
sorry for my late reply...now back to your question:
<you run the query and when the text box Me!DateOfIssueSB is filled in - no problem
Yes
<When you return and leave the same box empty, you get an error.
Yes (ie if I delete the date that was already there)
<Do you get an error if you leave it empty in the first place?
Not really. If I have never entered a date in Me!DateOfIssueSB on tblLetterOfCredit then I do not have any issue as the code was never ran. However, once I do enter a date and tab out then the code fires and no issue but if I delete that date I entered now or sometime later then I get the error.
What do you think now?
sorry for my late reply...now back to your question:
<you run the query and when the text box Me!DateOfIssueSB is filled in - no problem
Yes
<When you return and leave the same box empty, you get an error.
Yes (ie if I delete the date that was already there)
<Do you get an error if you leave it empty in the first place?
Not really. If I have never entered a date in Me!DateOfIssueSB on tblLetterOfCredit then I do not have any issue as the code was never ran. However, once I do enter a date and tab out then the code fires and no issue but if I delete that date I entered now or sometime later then I get the error.
What do you think now?
ASKER
just checking in....
What happens if you change this part of strSQL:
#" & Me!DateOfIssueSB & "#
to:
#" + Me!DateOfIssueSB + "#
by doing this, if ever the date were null, then an Null is inserted into the table - acceptable. With the former code it would try to insert ## if the DateOfIssueSB were Null - that would throw an error.
#" & Me!DateOfIssueSB & "#
to:
#" + Me!DateOfIssueSB + "#
by doing this, if ever the date were null, then an Null is inserted into the table - acceptable. With the former code it would try to insert ## if the DateOfIssueSB were Null - that would throw an error.
ASKER
darnit....still gave me an error.
Here is what I changed it to:
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" + Me!DateOfIssueSB + "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
if you think of something else let me know...thanks for sticking around on this one.
Here is what I changed it to:
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" + Me!DateOfIssueSB + "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
if you think of something else let me know...thanks for sticking around on this one.
What does the string look like using debug.print ??
ASKER
how do I do that?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am not sure if this is what you mean?
If Me!DateOfIssueSB = "" Then
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & Null & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
If Me!DateOfIssueSB = "" Then
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & "," & Null & ",'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
Else
strSQL = "INSERT INTO tblLCIssueDateHistory (fldDate, letterOfCreditID, EndUserID, IssueDate, LCNo, Amount) VALUES (#" & Format(Date, "m\/d\/yyyy") & "#," & Me!ID & "," & Me!EndUserID & ", #" & Me!DateOfIssueSB & "#,'" & HyperlinkPart(Me!LCNo, acDisplayText) & "'," & Me!Amount & ")"
End If
Is your underlying table set to disallow Null in this field? What ARE you allowing besides a valid date - empty string "", 0? You can use the Nz() function to convert a possible Null to "" or 0 or something else your table allows.
Re: SetWarnings
There's nothing wrong with turning them off. You need them to not show the confirmations to your users. If you use the CurrentDb.Execute sql, dbFailOnError, you don't need to turn the warnings off or back on.
Lee