Re: [MS_AccessPros] Update date field with Null

 

Duane-

I'll try to leave out the "#",see what would happen. By the way, if there're dates in the record, it works perfect. It won't work with null values.

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

__._,_.___

---

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

---

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

.

__,_._,___