Selasa, 02 Mei 2017

Re: [MS_AccessPros] Update date field with Null

 

Kevin-


You should be able to set any field except AutoNumber to Null - unless the Required property is set to Yes.

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 
(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写道：




Kevin,


Some fields don't allow Null. Have you checked your table design? If Nulls are allowed, then you should be able to use an update query to set the field to Null.


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 9:12 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Update date field with Null
 
John-
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写道：



 

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/
Welcome to the home of John Viescas Consulting. If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book ...

 
(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

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: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (24)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)