Search By Time (1 Viewer)

[TimTDP]

I have the table "tblTempTrainingDateTime"
It contains the fields:
TrainingTimeId - AutoNumber
TrainingTime - Date/Time & Format = Short Time

I have a record where the TrainingTime = 10:00:00

Why does the following return a null value?
lngTrainingTimeId = DLookup("TrainingTimeId", "tblTempTrainingDateTime", "TrainingTime = #10:00:00#")

Thanks in advance

 

[Ranman256]

the field is not just time, it is date/time.

I usu put the table in a query, all fields, and add either date only or time only:
TimeOnly: Format([TrainingTime],"hh:nn:ss")

then youj can query only time:
= DLookup("TrainingTimeId", "qsTempTrainingDateTime", "TimeOnly= #10:00:00#")

 

[Gasman]

Use the Time value function to compare just time?

 

[arnelgp]

the Format is for display purpose only.
it is still saving the Date portion (but not obvious).

lngTrainingTimeId = DLookup("TrainingTimeId", "tblTempTrainingDateTime", "TimeValue(TrainingTime) = #10:00:00#")

 

[TimTDP]

Thanks so much
Didn't know about function "TimeValue"

Thanks

 

[Pat Hartman]

Dates are stored as double precision numbers. The integer part is the number of days since 12/30/1899 (for MS Access. Excel and SQL Server use the same concept but a different origin date). Negative numbers are dates prior to 12/30/1899. The decimal part is the fraction of the day after midnight so .5 = noon. .25 = 6 AM. .75 = 6PM. So even if you think you are just storing time, you are not. You are actually storing a date as well. If you don't specify a date, the date is 0 which = 12/30/1899.

So, when you search for a time = #10:00:00# you may run into rounding "errors" as the decimal is converted to a string. If you use Now() to enter the time, you WILL have these issues but if you always enter the time manually, you shouldn't have any rounding issues.

If you use Now() to store the date/time, use a range rather than searching for equal values.

 

[Gasman]

Thanks so much
Didn't know about function "TimeValue"

Thanks

There is also a DateValue function when you want the date portion?