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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
GRayLCommented:
Please ignore the last half of my previous post.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
GRayLCommented:
I just tested and the Null works, everything else being okay.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
GRayLCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.