Re: [MS_AccessPros] Update date field with Null

 

John-

Thanks a lot. I am going to try it right now, and let you know later.

Best Regards,

Kevin

KEVIN Z.

Overprepare, then go with the flow.

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2017年5月2日 20:41写道：

 

Kevin-

If that's the case, then generate the SQL without the date filters:

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)

    If Len(strGroupStart) = 0 Or Len(strGroupEnd) = 0 Then

        strSQLThree = "UPDATE tblGroup SET GroupStart = Null WHERE GroupID = " & Me.Parent.GroupID

        strSQLFour = "UPDATE tblGroup SET GroupEnd = Null WHERE GrouprID = " & Me.Parent.GroupID

    Else
          strSQLThree = "UPDATE tblGroup SET [GroupStart] = #" &strGroupStart & "#" & " WHERE GroupID= " & Me.Parent.GroupID
         strSQLFour = "UPDATE tblGroup SET [GroupEnd] = #" & strGroupEnd & "#" & " WHERE GroupID = " & Me.Parent.GroupID
    End If

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

ErrExit:
    Exit Sub


ErrHandle:
   MsgBox "Error encountered: " & Err & ", " & Error
    Resume ErrExit

End Sub

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On May 2, 2017, at 2:29 PM, 'KEVIN Z.' qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John-

It works with valid dates. But I do have reasons to update blank values, for example, after having valid dates updated, then users might delete those records, when there's no dates again, those dates updated before will not erased. Please help!

Best Regards,

Kevin

KEVIN Z.

Overprepare, then go with the flow.

在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2017年5月2日 19:15写道：

Kevin-

Does it work when there are valid dates?  If so, then you simply need to check for "blank" dates and not run the queries.  After calculating strGroupEnd, do this:

If Len(strGroupStart) = 0 Or Len(strGroupEnd) = 0 Then Exit Sub

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On May 2, 2017, at 13:00, qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: "KEVIN Z." <qingqinga@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (17)

---

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

.

__,_._,___