Short time format problems

[JeroenMioch]

I have two unbound controls on a form that hold time values. The format in the propperty is set to "short time".
However this short time still displays medium time format with seconds.



One control is fine because i can set that to the system time like this :

ctrlEindToezichtHouden = Format(Time, "hh:nn")

However the other control is a time in the past, but whatever i try i cant get access to understand its a hh:nn format. i get type mismatch errors

Any clues ?

[ranman256]

Format(Time, "hh:nn") IS the correct way to view short time,
but beware that TIME here is the function and not a field, or vise versa.
Tho if both have time in it , then it should not throw an error.

ctrlEindToezichtHouden object may also have a FORMAT that is different from what you assigned.

[June7]

Format() function results in a text string, not a true date, time, or number.

Short Time for me displays only hh:nn in 24 hour clock structure. No idea why you still get seconds.

[ItsMe]

The format in the property is set to "short time".

If you are using VBA to format, remove formatting from the control's format property within the Property Sheet. Of course, if the control is enabled and or locked = No, the user will be able to type anything. Maybe an input mask will help in this case.

[JeroenMioch]

Thanks ItsMe,

I think it was a combination of formatting the propperty of the field and coding it in VBA.
The seconds are gone after i removed the time format on one of the controls i dimensioned in VBA.

Now another issue arises.
I want to put the subtraction of the endTime and StartTime into a text string, something like this :

Me.ctrlTextToReport = "Elapsed time is " & Me.ctrlElapsedTime & ""

But it gives me nothing.

[ItsMe]

I am not positive what your goal here is. If it is to do math with Time, you are going to need to use and understand a couple of data types ... String, Variant, Double, Long, Date (I think that is it).

So, the Date data type is something Microsoft provided for us to store and display Date and Time values. It is stored as a Double and displayed as Text. There is a lot that goes on behind the scenes. In addition, there are many built in functions provided by Microsoft to perform common tasks with Date and Time. If you want to compare Time with Time, you are going to have to do this via the double data type. So maybe you can explain the Business Rules here. Here is a reference that you will need.
https://support.microsoft.com/en-us/kb/210276

Basically, in order to compare time, you need to store the Date result as a double and also as an Integer. The integer will truncate the Time and leave you with a Days. What you do with the Integer/Days part depends on your objectives. I might be able top provide you with some examples, later. It depends on what you are trying to do.

Meanwhile, here are some data type conversions to confuse the heck out of you. I am leaving out the Integer thing. Just get used to the Date thing converting to Double, for now. Also, you might start to see limitations of the many built in functions, like DateDiff.

Dim dblCurrentTime As Double
Dim dblElapsedTime As Double
Dim dtCurrentTime As Date
Dim dtElapsedTime As Date
Dim dtResult As Date
Dim dtDateDiff As Date
Dim lngTimeDiff As Long

dblCurrentTime = Now
dtCurrentTime = dblCurrentTime
dblElapsedTime = dtCurrentTime + 5
dtElapsedTime = dblElapsedTime
dtResult = dblElapsedTime

lngTimeDiff = DateDiff("s", dtCurrentTime, dtElapsedTime)

Debug.Print "Result Value as a Date type from a double: " & dtResult
Debug.Print "Time in Text format from a Variant: " & TimeValue(dtResult)
Debug.Print "Difference between dates as a Long: " & lngTimeDiff

[JeroenMioch]

Ill explain what i am doing ;

We are security and we are providing services for departements.
We are keeping track on how long our people provide these services.

So if i leave my desk at lets say 19:00 hrs and i click a button and the current time is displayed in a non-bound control (ctrlStartToezichtHouden).
Then we click some other fields wich do not matter in this case.
When i return at my desk at lets say 19:12 hrs i click another button and the current time is displayed in another non-bound control (ctrlEindToezichtHouden).

What i want is that the difference is calculated in a third control (ctrlElapsedTime).
After the end time is clicked the values from the unbound controls are transferred to a table.
An underlaying query (wich is the forms recordsource) calculates the ElapsedTime perfectly by the way.
However i cannot use this calculation for the unbound field.

I am using the unbound fields because i want the form to have the record i am adding on top of the form (in the header section), and then i sort the records in the detail section based on date and time so i have a chronological overview from new to old.
Furthermore, my code copys some details wich i can paste in a word file (daily report).

This looks something like this : "Surveillance done for event services departement, at expedition entrance building X for the duration of 00:12 minutes."

The whole line of text is created based on my unbound controls. It works fine but for the last part (the duration).
So in stead of typing into an excel sheet and then again into the daily report, my coworkers do some clicking in stead of typing, wich saves alot of time.

Im gonna try dimensioning some things like you stated and read up on the matter.
Im curious why access adds seconds to some fields while i specificly say that its 'short time' and not 'medium time' (wich do has seconds).

Ill keep you posted, thanks for the reply mate, apreciated as allways

[ItsMe]

An underlaying query (wich is the forms recordsource) calculates the ElapsedTime perfectly by the way.

And then it displays the result as a Variant to you, the human. For instance 5 hours (integer). Or 32900 seconds (long Integer). Actually, because it is in a query, it is probably a string of text. So you will have to do the appropriate conversion to Cast it to a control. Again, this is where things get confusing. Formatting the Text of a control is not affecting the underlying data. When you tell a control to Format as Short Time, it wants a double (42535.658) The following is not a valid Date data type, 10:15. The following is the result of a calculation, 10:15. The following is text, 10:15. You cannot Cast the following to a Date data type, 10:15.

Im curious why access adds seconds to some fields while i specificly say that its 'short time' and not 'medium time' (wich do has seconds

Access does not change the underlying data when you choose short time vs. medium time. 42535.658 formatted as Short Time is still 42535.658 when formatted as Long Time.