Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Update Time Portion Was Of Date

Posted by: CCSlice Feb 25 2019, 09:05 AM

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

Posted by: cheekybuddha Feb 25 2019, 09:31 AM

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

Posted by: cheekybuddha Feb 25 2019, 09:34 AM

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

Posted by: cheekybuddha Feb 25 2019, 09:37 AM

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

Posted by: CCSlice Feb 25 2019, 10:42 AM

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.

Posted by: cheekybuddha Feb 25 2019, 11:39 AM

Did you try the suggestions?

Posted by: CCSlice Feb 28 2019, 08:30 PM

Hi David,

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