ACCESS FEED: Re: [MS_AccessPros] Update date field with Null

John-

It works GREAT! Thanks a lot. And Thanks to Duane.

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 (18)

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

__,_._,___

ACCESS FEED

Selasa, 02 Mei 2017

Re: [MS_AccessPros] Update date field with Null

Tidak ada komentar:

Posting Komentar