auto fill time gets 12/31/1899

[tubar]

when the user double clicks in the "time out" field 4 hours are added from the value in the time in field to create the value in time out...both fields format are at medium time but i still get 12/31/1899 infront of the time. the vba i use on dblclick time out is

Me.Time_Out.Value = DateAdd("h", 4, Me.Time_in.Value)

any suggestions???

 

[TJPoorman]

do you have code or default value that sets time_in to now()?

 

[Pat Hartman]

The datetime data type is stored as a double precision number with the integer portion representing the number of days since Dec 30, 1899. This is the origin date and its value is 0. So 1 is Dec 31, 1899 and -1 is Dec 29, 1899.

So, that tells me that you are only storing a time value but not a date value and so the date defaults to 0 or Dec 30, 1899.

If you don't want to store the date, change the display of the control to show only time and in your queries for reports, use the TimeValue function to extract just the time of day.

Keep in mind that datetime fields are intended to hold a point in time, NOT elapsed time which can exceed 24 hours.

 

[tubar]

time in is manually inputted..my issue only happens when the time out crosses the date line...
example time in 8:30pm and time out is 12:30am the following day...

since most of the calls are 4 hours...instead of typing in 12:30am...the user double clicks time out and
the vba adds 4 hours to time in and places the value in time out.

i have the display of the control to show only time...however i still get the issue where dec 30,1899 is displayed infront of the time.
any more suggestions

 

[Pat Hartman]

There is no way to make a datetime field NOT store a date. So either use Date() to store the current date (my preference) or format the field as I said in my earlier post to NOT show date at all.

If start and end time can cross midnight, then you should store the date. If you don't, you will not be able to accurately calculate the elapsed time.

 

[TJPoorman]

Pat is right, using the "Format" of a control only changes how it is displayed not how it is stored.
You could also use the format() function to in your click event to extract only the time.

 

[boblarson]

If you include the date, it becomes way easier in calculating elapsed time. The people inputting don't need to deal with the date or enter it. Just do as Pat said and use Date() + the value of the inputted time.