UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Time Portion Was Of Date, Access 2013    
 
   
CCSlice
post Feb 25 2019, 09:05 AM
Post#1



Posts: 191
Joined: 12-August 07
From: Toronto, Canada


Hello UA Nation,

I have a table of 25,000 dates (in the form Feb 23, 2019 07:00:00 AM).
I would need to change 07:00:00 AM to 10:00:00 AM without disrupting the calendar dates.
Could someone show how this can be done?

Thanks in advance for your help
Go to the top of the page
 
cheekybuddha
post Feb 25 2019, 09:31 AM
Post#2


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


Hi,

Back up your table first!!!

Then try:
CODE
  Dim strSQL As String

  strSQL = "UPDATE YourTable SET DateField = Int(DateField) + 0.4166666667;"
  CurrentDb.Execute strSQL, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 25 2019, 09:34 AM
Post#3


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


If only some of the dates need to be updated (ie not all are 07:00:00) then try:
CODE
  Dim strSQL As String

  strSQL = "UPDATE YourTable SET DateField = Int(DateField) + 0.4166666667 WHERE DateField - Int(DateField) = 0.2916666667;"
  CurrentDb.Execute strSQL, dbFailOnError

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 25 2019, 09:37 AM
Post#4


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


Actually, probably better not to use a constant value.

Try:
CODE
  Dim strSQL As String

  strSQL = "UPDATE YourTable SET DateField = Int(DateField) + #10:00:00# WHERE DateField - Int(DateField) = #07:00:00#;"
  CurrentDb.Execute strSQL, dbFailOnError


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
CCSlice
post Feb 25 2019, 10:42 AM
Post#5



Posts: 191
Joined: 12-August 07
From: Toronto, Canada


Hi,
Thanks for the responses everyone. It is not the dates that need to be updated but the times need to be updated.

So,
02/03/2019 04:00:00 PM

Needs to be updated to

02/03/2019 04:30:00 PM

I would need to change Start and End Times for schedules that are linked to a SharePoint calendar, so I need to be accurate in doing this. I have tried using the TimeVaiue function only to update (in a test Table!) only the 02/03/2019 remaining.
Go to the top of the page
 
cheekybuddha
post Feb 25 2019, 11:39 AM
Post#6


UtterAccess VIP
Posts: 11,419
Joined: 6-December 03
From: Telegraph Hill


Did you try the suggestions?

--------------------


Regards,

David Marten
Go to the top of the page
 
CCSlice
post Feb 28 2019, 08:30 PM
Post#7



Posts: 191
Joined: 12-August 07
From: Toronto, Canada


Hi David,

Yes. I have tried your SQL and it works! Thanks for the big assist!

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 11:13 PM