Date to date and time. (1 Viewer)

[Bieke]

Hello,

I have 2 date and time fields in a table field1 : starttime and field2 : stoptime.
I have a record where field2 (stoptime) with date 12/03/2021 23:15:40 (hh:mm:ss). Now i want to write in field1 of this record the value 12/03/2021 00:00:00 to calculate afterwards the difference from midnight to stoptime. So i only have this stoptime to create this midnight value from. Someone any idee how to do this?

Thanks in advance,
Bieke

 

[MajP]

In access all fields are dateTime fields they may be formatted to only show date or only show time. There is always a date part and a time part. Just format the display to show the time component.

 

[Bieke]

In access all fields are dateTime fields they may be formatted to only show date or only show time. There is always a date part and a time part. Just format the display to show the time component.

i have 12/03/2021 23:15:40 and i want to create a new datetime value 12/03/2021 00:00:00.
I need to write this value (12/03/2021 00:00:00) to a field starttime in a new record of a table : this record has 2 datetime field, starttime and stoptime. How do i create this 12/03/2021 00:00:00 value.

 

[theDBguy]

Hi. Sounds to me like you don't even need the start field, if all it will contain is midnight. The time difference between midnight and any time is equal to that time.

In any case, to generate a midnight from a date and time value, you can use the DateValue() or Int() function.

 

[arnelgp]

bring your table in design view and add Format do your Date fields:

mm/dd/yyyy hh:nn:ss

now, just Update date1 field with Date().

 

[oleronesoftwares]

You will write in the control source of the control at form level the following
=Format([startdatetime],"dd/mm/yyyy hh:nn:ss")

See the images attached

 

[Bieke]

Hi. Sounds to me like you don't even need the start field, if all it will contain is midnight. The time difference between midnight and any time is equal to that time.

In any case, to generate a midnight from a date and time value, you can use the DateValue() or Int() function.

Can you give me an example how do use these functions to create a midnight out of a date (Generate 24/11/2021 00:00:00 from 24/11/2021 13:05:25)

 

[The_Doc_Man]

To make date B equal to midnight of the same date as in date A (where A might have a time portion):

DateB = CDate( CLng( DateA ) )

 

[Bieke]

To make date B equal to midnight of the same date as in date A (where A might have a time portion):

DateB = CDate( CLng( DateA ) )

Then this is the result ....

And this is the code :

 

[arnelgp]

it will Not show the 0:00:00.000 there if you do not have any Formatting
to the field.
even if How many times you update that field.

 

[theDBguy]

Can you give me an example how do use these functions to create a midnight out of a date (Generate 24/11/2021 00:00:00 from 24/11/2021 13:05:25)

Sure. For example:

DateValue([DTSSTOP])

 

[The_Doc_Man]

From your response, I have to agree with arnelgp. Your problem is a case of "default" vs "desired" behavior. In the absence of a time -part (i.e. the date/time field is for midnight) then Access responds with the shortest string that accurately represents the date/time. Since midnight is also the default time for a date presented without a time part, Access sees no need to show defaults. However, when a date/time field includes a time other than midnight, Access shows you ... the shortest string that accurately represents the date/time - which now includes a time string.

The solution is to force the format. In a query or using VBA code to assign a string value, you can use Format( date-time field, "yyyy/mm/dd hh:nn:ss" ) to create this string. Since this IS a string, you cannot use it in further computations without extra formatting.

HOWEVER, it is also possible to format the field on the form using the targeted control's Format properties. This doesn't change the underlying field type, just changes how you will see it.

You can also leave the query field in its normal type (i.e. no typecast or function applied) but instead, in design mode on the query, select that field to open its properties and define a format string (like the one I used in the 2nd argument of the Format function earlier in this post.) Selecting the format of a field doesn't change the data type of the field so this should leave the query with a date/time field in that slot, which would then be usable in subsequent date computations and comparisons.

 

[Bieke]

Indeed, the time is not showing but it is there, this was my confusion. I now can calculate the difference between stoptime and midnight. Problem solved. Thanks to you all for helping me out.
Happy new year !!

 

[theDBguy]

Indeed, the time is not showing but it is there, this was my confusion. I now can calculate the difference between stoptime and midnight. Problem solved. Thanks to you all for helping me out.
Happy new year !!

But have you noticed about what I said earlier? The difference between stoptime and midnight should be equal to stoptime.

 

[arnelgp]

stoptime and midnight should be equal to stoptime.

more precisely, the TimeValue of stoptime.