Re: [MS_AccessPros] Update date field with Null

 

Kevin,

You need to test for empty date memory variables and use some type of conditional logic.

    Dim strGroupStart As String
    Dim strGroupEnd As String
    Dim strSQLThree As String
    Dim strSQLFour As String

    strGroupStart = Nz(Format(DMin("PartItineraryDate", "qryGetGroupStartEnd", "GroupID=" & Me.Parent.GroupID), "dd-mmm-yyyy"), Null)

    If Len(strGroupStart) = 0 Then   'check for no value
        strGroupStart = " Null "
      Else
        strGroupStart = "#" & strGroupStart & "#"   'add the delimiters here, not later
    End If
    Debug.Print "strGroupStart = " & strGroupStart

    strGroupEnd = Nz(Format(DMax("PartItineraryDate", "qryGetGroupStartEnd", "GroupID=" & Me.Parent.GroupID), "dd-mmm-yyyy"), Null)
    If Len(strGroupEnd) = 0 Then
        strGroupEnd = " Null "
      Else
        strGroupEnd = "#" & strGroupEnd & "#"
    End If
    Debug.Print "strGroupEnd = " & strGroupEnd

    strSQLThree = "UPDATE tblGroup SET [GroupStart] = " & strGroupStart & " WHERE GroupID = " & Me.Parent.GroupID
    Debug.Print "strSQLThree = " & strSQLThree
 
    strSQLFour = "UPDATE tblGroup SET [GroupEnd] = " & strGroupEnd & " WHERE GroupID = " & Me.Parent.GroupID
    Debug.Print "strSQLFour = " & strSQLFour

    ' after running the code, open the debug window (press Ctrl+G) to check the results.

    CurrentDb.Execute strSQLThree, dbFailOnError
    CurrentDb.Execute strSQLFour, dbFailOnError

Duane Hookom

Switzerland

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, May 2, 2017 6:00 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Update date field with Null

 

Duane-

After running the debug.print as your instruction, when there's dates:

strGroupStart = 01-Jan-2018
strGroupEnd = 01-Jan-2018
strSQLThree = UPDATE tblGroup SET [GroupStart] = #01-Jan-2018# WHERE GroupID = 1
strSQLFour = UPDATE tblGroup SET [GroupEnd] = #01-Jan-2018# WHERE GroupID = 1


If there's no dates in the records, the outcome is like this:

strGroupStart =
strGroupEnd =
strSQLThree = UPDATE tblGroup SET [GroupStart] = ## WHERE GroupID = 1
strSQLFour = UPDATE tblGroup SET [GroupEnd] = ## WHERE GroupID = 1

Please help!

Best Regards,
Kevin

Zhao LiQing

Be adventurous, be bold, be careful, be a star !

--------------------------------------------
On Tue, 5/2/17, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 Subject: Re: [MS_AccessPros] Update date field with Null
 To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
 Date: Tuesday, May 2, 2017, 6:23 PM
 
 
  
 
 
 
  
 
 
    
      
      
      
 
 
 
 Kevin,
 
 
 
 Debug.Print is a line of code that many of us use to test
 values of memory variables etc. I would add it to your code
 like:
 
 
 
 
     strGroupStart =
 Nz(Format(DMin("PartItineraryDate",
 "qryGetGroupStartEnd", "GroupID=" &
 Me.Parent.GroupID), "dd-mmm-yyyy"), Null)
 
 
     debug.Print "strGroupStart
 = " & strGroupStart 
 
 
 
     strGroupEnd =
 Nz(Format(DMax("PartItineraryDate",
 "qryGetGroupStartEnd", "GroupID=" &
 Me.Parent.GroupID), "dd-mmm-yyyy"), Null)
 
 
 
     debug.Print "strGroupEnd
 = " &
 strGroupEnd
 
 
 
 
     strSQLThree = "UPDATE tblGroup
 " & "SET [GroupStart] = #" &
 strGroupStart & "#" & " WHERE GroupID
 = " & Me.Parent.GroupID
 
     debug.Print "strSQLThree
 = " & strSQLThree
 
  
 
 
     strSQLFour = "UPDATE tblGroup
 " & "SET [GroupEnd] = #" &
 strGroupEnd & "#" & " WHERE GroupID =
 " & Me.Parent.GroupID
 
 
 
     debug.Print "strSQLFour
 = " & strSQLFour
 
 
 
 
 
     ' after running the code, open the debug
 window (press Ctrl+G) to check the results.
 
 
 
 
 
     CurrentDb.Execute strSQLThree,
 dbFailOnError
 
 
     CurrentDb.Execute strSQLFour,
 dbFailOnError
 
 
 
 
 Regards,
 Duane Hookom
 Switzerland
 
 
 
 
 
 
 
 
 From:
 MS_Access_Professionals@yahoogroups.com
 <MS_Access_Professionals@yahoogroups.com> on behalf of
 'KEVIN Z.' qingqinga@yahoo.com
 [MS_Access_Professionals]
  <MS_Access_Professionals@yahoogroups.com>
 
 Sent: Tuesday, May 2, 2017 5:05 AM
 
 To: MS_Access_Professionals@yahoogroups.com
 
 Subject: Re: [MS_AccessPros] Update date field with
 Null
  
 
 
 Duane-
 
 
 By the way, after running debug.print. the
 error is:
 
 
 Run-time error '424':
 
 
 Object required
 
 
 Best Regards,
 
 
 Kevin
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 KEVIN Z.
 
 
 Overprepare, then go with the flow.
 在 "Duane Hookom
 duanehookom@hotmail.com [MS_Access_Professionals]"
 <MS_Access_Professionals@yahoogroups.com>，2017年5月2日
 17:27写道：
 
 
 
 
 
 
 
  
 
 
 
 
 
 Kevin,
 
 
 
 What happened to the suggest Debug.Print statement? Which
 line causes the error? What are the values stored in the
 memory variables? 
 
 
 
 Your code doesn't match what I suggested. If
 strGroupStart or strGroupEnd are null, you can't use the
 "#" delimiters which are used for actual date
 values.
 
 
 
 If you want to resolve some of these issues you will need
 to implement trouble-shooting functionality. You can also
 set a breakpoint and step through your code.
 
 
 
 
 
 
 Regards,
 Duane Hookom
 Switzerland
 
 
 
 
 
 
 
 
 From:
 MS_Access_Professionals@yahoogroups.com
 <MS_Access_Professionals@yahoogroups.com> on behalf of
 'KEVIN Z.' qingqinga@yahoo.com
 [MS_Access_Professionals]
  <MS_Access_Professionals@yahoogroups.com>
 
 Sent: Tuesday, May 2, 2017 3:33 AM
 
 To: MS_Access_Professionals@yahoogroups.com
 
 Subject: Re: [MS_AccessPros] Update date field with
 Null
  
 
 
 Duane-
 
 
 Here's the code:
 
 
 Private Sub Command18_Click()
 
 
 On Error GoTo ErrHandle
 
 
 
 
 
     Dim strGroupStart As String
 
 
     Dim strGroupEnd As String
 
 
     Dim strSQLThree As String
 
 
     Dim strSQLFour As String
 
 
  
 
 
 
 
 
     strGroupStart =
 Nz(Format(DMin("PartItineraryDate",
 "qryGetGroupStartEnd", "GroupID=" &
 Me.Parent.GroupID), "dd-mmm-yyyy"), Null)
 
 
     strGroupEnd =
 Nz(Format(DMax("PartItineraryDate",
 "qryGetGroupStartEnd", "GroupID=" &
 Me.Parent.GroupID), "dd-mmm-yyyy"), Null)
 
 
 
 
 
     strSQLThree = "UPDATE tblGroup
 " & "SET [GroupStart] = #" &
 strGroupStart & "#" & " WHERE GroupID
 = " & Me.Parent.GroupID
 
 
     strSQLFour = "UPDATE tblGroup
 " & "SET [GroupEnd] = #" &
 strGroupEnd & "#" & " WHERE GroupID =
 " & Me.Parent.GroupID
 
 
 
 
 
 
 
 
 
 
 
     CurrentDb.Execute strSQLThree,
 dbFailOnError
 
 
     CurrentDb.Execute strSQLFour,
 dbFailOnError
 
 
 
 
 
 ErrExit:
 
 
     Exit Sub
 
 
 ErrHandle:
 
 
     Resume ErrExit
 
 
 End Sub
 
 
 
 
 
 Best Regards,
 
 
 Kevin
 
 
 
 
 
 
 
 
 
 
 
 KEVIN Z.
 
 
 Overprepare, then go with the flow.
 在 "Duane Hookom
 duanehookom@hotmail.com [MS_Access_Professionals]"
 <MS_Access_Professionals@yahoogroups.com>，2017年5月2日
 16:20写道：
 
 
 
 
 
 
 
  
 
 
 
 
 
 Kevin,
 
 
 
 We can't see your screen. What line causes the popup?
 You have provided only one line of code. I expect there are
 more that you haven't shared.
 
 
 
 Regards,
 Duane Hookom
 Switzerland
 
 
 
 
 
 
 From:
 MS_Access_Professionals@yahoogroups.com
 <MS_Access_Professionals@yahoogroups.com> on behalf of
 'KEVIN Z.' qingqinga@yahoo.com
 [MS_Access_Professionals]
  <MS_Access_Professionals@yahoogroups.com>
 
 Sent: Tuesday, May 2, 2017 3:12 AM
 
 To: MS_Access_Professionals@yahoogroups.com
 
 Subject: Re: [MS_AccessPros] Update date field with
 Null
  
 
 
 Duane-
 
 
 
 
 
 Thanks a lot for your quick reply. The popup
 says:
 
 
 Run-time error '424':
 
 
 Object required
 
 
 
 
 
 Best Regards,
 
 
 Kevin
 
 
 
 
 
 
 
 
 
 
 
 KEVIN Z.
 
 
 Overprepare, then go with the flow.
 在 "Duane Hookom
 duanehookom@hotmail.com [MS_Access_Professionals]"
 <MS_Access_Professionals@yahoogroups.com>，2017年5月2日
 14:34写道：
 
 
 
 
 
 
 
  
 
 
 
 
 
 
 
 
 Kevin,
 
 
 
 Try:
 strSQLThree = "UPDATE tblGroup SET
 [GroupStart] = Null WHERE GroupID = " &
 Me.GroupID
 Debug.Print strSQLThree   ' show me the
 SQL
 
 
 
 Regards,
 Duane Hookom
 Switzerland
 
 
 
 
 
 From:
 MS_Access_Professionals@yahoogroups.com
 <MS_Access_Professionals@yahoogroups.com> on behalf of
 'KEVIN Z.' qingqinga@yahoo.com
 [MS_Access_Professionals]
  <MS_Access_Professionals@yahoogroups.com>
 
 Sent: Tuesday, May 2, 2017 1:19 AM
 
 To: ms_access_professionals
 
 Subject: [MS_AccessPros] Update date field with
 Null
  
 
 
 
 
 
 
 
 
 Dear All,
 
 
 I was trying to update field with Null value,
 bu failed. Please help! Thanks in advance.
 
 
 Here's the code.
 
 
 strSQLThree = "UPDATE tblGroup "
 & "SET [GroupStart] = #" & Null &
 "#" & " WHERE GroupID = " &
 Me.GroupID
 
 
 
 
 
 Best Regards,
 
 
 Kevin
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
    
     
 
    
    
 
 
 
 #yiv1194033310 #yiv1194033310 --
   #yiv1194033310ygrp-mkp {
 border:1px solid #d8d8d8;font-family:Arial;margin:10px
 0;padding:0 10px;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp hr {
 border:1px solid #d8d8d8;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp #yiv1194033310hd {
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 0;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp #yiv1194033310ads {
 margin-bottom:10px;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp .yiv1194033310ad {
 padding:0 0;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp .yiv1194033310ad p {
 margin:0;}
 
 #yiv1194033310 #yiv1194033310ygrp-mkp .yiv1194033310ad a {
 color:#0000ff;text-decoration:none;}
 #yiv1194033310 #yiv1194033310ygrp-sponsor
 #yiv1194033310ygrp-lc {
 font-family:Arial;}
 
 #yiv1194033310 #yiv1194033310ygrp-sponsor
 #yiv1194033310ygrp-lc #yiv1194033310hd {
 margin:10px
 0px;font-weight:700;font-size:78%;line-height:122%;}
 
 #yiv1194033310 #yiv1194033310ygrp-sponsor
 #yiv1194033310ygrp-lc .yiv1194033310ad {
 margin-bottom:10px;padding:0 0;}
 
 #yiv1194033310 #yiv1194033310actions {
 font-family:Verdana;font-size:11px;padding:10px 0;}
 
 #yiv1194033310 #yiv1194033310activity {
 background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}
 
 #yiv1194033310 #yiv1194033310activity span {
 font-weight:700;}
 
 #yiv1194033310 #yiv1194033310activity span:first-child {
 text-transform:uppercase;}
 
 #yiv1194033310 #yiv1194033310activity span a {
 color:#5085b6;text-decoration:none;}
 
 #yiv1194033310 #yiv1194033310activity span span {
 color:#ff7900;}
 
 #yiv1194033310 #yiv1194033310activity span
 .yiv1194033310underline {
 text-decoration:underline;}
 
 #yiv1194033310 .yiv1194033310attach {
 clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
 0;width:400px;}
 
 #yiv1194033310 .yiv1194033310attach div a {
 text-decoration:none;}
 
 #yiv1194033310 .yiv1194033310attach img {
 border:none;padding-right:5px;}
 
 #yiv1194033310 .yiv1194033310attach label {
 display:block;margin-bottom:5px;}
 
 #yiv1194033310 .yiv1194033310attach label a {
 text-decoration:none;}
 
 #yiv1194033310 blockquote {
 margin:0 0 0 4px;}
 
 #yiv1194033310 .yiv1194033310bold {
 font-family:Arial;font-size:13px;font-weight:700;}
 
 #yiv1194033310 .yiv1194033310bold a {
 text-decoration:none;}
 
 #yiv1194033310 dd.yiv1194033310last p a {
 font-family:Verdana;font-weight:700;}
 
 #yiv1194033310 dd.yiv1194033310last p span {
 margin-right:10px;font-family:Verdana;font-weight:700;}
 
 #yiv1194033310 dd.yiv1194033310last p
 span.yiv1194033310yshortcuts {
 margin-right:0;}
 
 #yiv1194033310 div.yiv1194033310attach-table div div a {
 text-decoration:none;}
 
 #yiv1194033310 div.yiv1194033310attach-table {
 width:400px;}
 
 #yiv1194033310 div.yiv1194033310file-title a, #yiv1194033310
 div.yiv1194033310file-title a:active, #yiv1194033310
 div.yiv1194033310file-title a:hover, #yiv1194033310
 div.yiv1194033310file-title a:visited {
 text-decoration:none;}
 
 #yiv1194033310 div.yiv1194033310photo-title a,
 #yiv1194033310 div.yiv1194033310photo-title a:active,
 #yiv1194033310 div.yiv1194033310photo-title a:hover,
 #yiv1194033310 div.yiv1194033310photo-title a:visited {
 text-decoration:none;}
 
 #yiv1194033310 div#yiv1194033310ygrp-mlmsg
 #yiv1194033310ygrp-msg p a span.yiv1194033310yshortcuts {
 font-family:Verdana;font-size:10px;font-weight:normal;}
 
 #yiv1194033310 .yiv1194033310green {
 color:#628c2a;}
 
 #yiv1194033310 .yiv1194033310MsoNormal {
 margin:0 0 0 0;}
 
 #yiv1194033310 o {
 font-size:0;}
 
 #yiv1194033310 #yiv1194033310photos div {
 float:left;width:72px;}
 
 #yiv1194033310 #yiv1194033310photos div div {
 border:1px solid
 #666666;height:62px;overflow:hidden;width:62px;}
 
 #yiv1194033310 #yiv1194033310photos div label {
 color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}
 
 #yiv1194033310 #yiv1194033310reco-category {
 font-size:77%;}
 
 #yiv1194033310 #yiv1194033310reco-desc {
 font-size:77%;}
 
 #yiv1194033310 .yiv1194033310replbq {
 margin:4px;}
 
 #yiv1194033310 #yiv1194033310ygrp-actbar div a:first-child {
 margin-right:2px;padding-right:5px;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg {
 font-size:13px;font-family:Arial, helvetica, clean,
 sans-serif;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg table {
 font-size:inherit;font:100%;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg select,
 #yiv1194033310 input, #yiv1194033310 textarea {
 font:99% Arial, Helvetica, clean, sans-serif;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg pre, #yiv1194033310
 code {
 font:115% monospace;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg * {
 line-height:1.22em;}
 
 #yiv1194033310 #yiv1194033310ygrp-mlmsg #yiv1194033310logo {
 padding-bottom:10px;}
 
 
 #yiv1194033310 #yiv1194033310ygrp-msg p a {
 font-family:Verdana;}
 
 #yiv1194033310 #yiv1194033310ygrp-msg
 p#yiv1194033310attach-count span {
 color:#1E66AE;font-weight:700;}
 
 #yiv1194033310 #yiv1194033310ygrp-reco
 #yiv1194033310reco-head {
 color:#ff7900;font-weight:700;}
 
 #yiv1194033310 #yiv1194033310ygrp-reco {
 margin-bottom:20px;padding:0px;}
 
 #yiv1194033310 #yiv1194033310ygrp-sponsor #yiv1194033310ov
 li a {
 font-size:130%;text-decoration:none;}
 
 #yiv1194033310 #yiv1194033310ygrp-sponsor #yiv1194033310ov
 li {
 font-size:77%;list-style-type:square;padding:6px 0;}
 
 #yiv1194033310 #yiv1194033310ygrp-sponsor #yiv1194033310ov
 ul {
 margin:0;padding:0 0 0 8px;}
 
 #yiv1194033310 #yiv1194033310ygrp-text {
 font-family:Georgia;}
 
 #yiv1194033310 #yiv1194033310ygrp-text p {
 margin:0 0 1em 0;}
 
 #yiv1194033310 #yiv1194033310ygrp-text tt {
 font-size:120%;}
 
 #yiv1194033310 #yiv1194033310ygrp-vital ul li:last-child {
 border-right:none !important;
 }
 #yiv1194033310
 


------------------------------------
Posted by: <zhao.liqing@yahoo.com>
------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/MS_Access_Professionals/join
    (Yahoo! ID required)

<*> To change settings via email:
    MS_Access_Professionals-digest@yahoogroups.com
    MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
    https://info.yahoo.com/legal/us/yahoo/utos/terms/

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (14)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___