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_BeforeUpdate(Cancel As Integer)

       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
pdvsaProject financeAsked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
I just tested and the Null works, everything else being okay.
0
 
lee555J5Commented:
Where is the [DATEOFISSUE] in the SQL? Is it this Me!DateOfIssueSB?

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
0
 
GRayLCommented:
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 & ")"
 
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
GRayLConnect With a Mentor Commented:
Please ignore the last half of my previous post.
0
 
GRayLCommented:
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
0
 
pdvsaProject financeAuthor Commented:
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?

 syntax error debugger DateOfIssue
0
 
pdvsaProject financeAuthor Commented:
Gray:  did not see your post.... will read it
0
 
pdvsaProject financeAuthor Commented:
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...
0
 
pdvsaProject financeAuthor Commented:
the debugger highlights the   DoCmd.RunSQL strSQL  line
0
 
GRayLCommented:
Replace ,, with ,Null, in the first SQL
0
 
pdvsaProject financeAuthor Commented:
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_BeforeUpdate(Cancel As Integer)

       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
0
 
GRayLCommented:
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 & ")"
 
0
 
pdvsaProject financeAuthor Commented:
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?
0
 
GRayLCommented:
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?
0
 
pdvsaProject financeAuthor Commented:
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?
0
 
pdvsaProject financeAuthor Commented:
just checking in....
0
 
GRayLCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
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.  
0
 
GRayLCommented:
What does the string look like using debug.print ??
0
 
pdvsaProject financeAuthor Commented:
how do I do that?  
0
 
GRayLConnect With a Mentor Commented:
The debug print will place the string with all the inclusions in the Immediate Pane of the VB Editor.  Get there by typing Alt+F11.  Highlight the string, press Ctl+C to copy, open a new post back here, and press Ctl+V to paste.
0
 
pdvsaProject financeAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.