John-
Yesterday, I have learned how to update a date field to Null, and it works perfectly well.
Today I chanllenged myself with other type of fields. I failed again. Here's the code I use:
If IsNull(Me.Parent.PartItineraryID) Then
Dim strSQLTWO As String
strSQLTWO = "UPDATE tblPart SET [FromCityID] = Null, " & _
"[ToCityID] = Null, " & _
"[FromCityEN] = Null, " & _
"[ToCityEN] = Null WHERE PartID = " & Me.Parent.PartID
End If
FromCityID and toCityID are Number
FromCityEN and ToCityEN are text field
I tried to use Debug.Print "strSQLTWO = " & strSQLTWO, there's nothing show. Can this be solved? or I have to find other ways to get the expected result?
Thanks in advance.
Best Regards,
Kevin
KEVIN Z.
Overprepare, then go with the flow.
在 "'KEVIN Z.' qingqinga@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2017年5月3日 05:29写道：
John-
I got it. Thanks a lot. I am going to adjust the code I wrote before. It's going to be a lof of work.
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日 23:37写道：Kevin-
You should be able to set any field except AutoNumber to Null - unless the Required property is set to Yes.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On May 2, 2017, at 4:42 PM, 'KEVIN Z.' qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Duane-
I will check my table design tomorrow, It's quite late here.
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日 22:33写道：
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 9:12 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Update date field with NullJohn-
Can I use the same method to set a text field to null instead of dates? "SET SomeTextField = Null ..." seems not working.
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写道：
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 ThenstrSQLThree = "UPDATE tblGroup SET GroupStart = Null WHERE GroupID = " & Me.Parent.GroupIDstrSQLFour = "UPDATE tblGroup SET GroupEnd = Null WHERE GrouprID = " & Me.Parent.GroupIDElse
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, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applicationshttp://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, authorEffective SQLSQL Queries for Mere MortalsMicrosoft Office Access 2010 Inside OutMicrosoft Office Access 2007 Inside OutBuilding Access Applications
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 p
__._,_.___
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 (26) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar