Change Date/Time Field 1

[ptrifile]

Hi, I have a simple query that pulls from 1 table. One of the fields is a date/time field. I need to change the date/time entries to all the same time. Here is what I am trying to accomplish:

Date field
5/27/2020 11:29:00
5/27/2020 8:30:00
5/26/2020 15:35:35

Desired results from query:
5/27/2020 23:59:59
5/27/2020 23:59:59
5/26/2020 23:59:59

Any suggestions on how I can do this?

Thank you,

Paul

 

[Andrzejek]

Long explanation:

Dim dat As Date
dat = CDate("5/27/2020 11:29:00")
dat = DateSerial(Year(dat), Month(dat), Day(dat))
dat = CDate(dat & " 23:59:59")
Debug.Print dat

Dim strSQL As String
strSQL = "Update MyTable Set MyDateField = " & dat
Debug.Print strSQL
'or
strSQL = "Update MyTable Set MyDateField = #" & dat & "#"
Debug.Print strSQL

I am sure there is a better, shorter way...


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

ptrfile,
Is this a permanent change to the data or just for display? If it is permanent then you would use an update query to alter the data. Otherwise you can just display the results.

The expression you need is:
DateValue([Date Field]) + 0.99999

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Thanks for the suggestions guys, much appreciated.

Duane, I am only looking to show the results as I will be exporting the query to a txt file to upload in to another system. Your solution worked but it shows as "43978.9999", I understand that acutally is the date and time I am looking for but is there a way to have it show 5/27/2020 11:59:59 instead of the number?

Thanks for the help!

Paul

 

[dhookom]

Try:
DateAdd("s",-1,DateValue([Date Field])+1)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

That worked perfectly! Thank you!