Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Date + Time _ Dlookup Using A Time As Criteria

Posted by: TimTDP Jun 24 2019, 06:14 AM

Sorry Guys, but I am having a real problem doing a DLookup using a time as criteria. I have the following:

Dim strNewTime As String
Dim timeNewTime As Date
Dim answer As Long

strNewTime = InputBox("Enter training time as hh:mm", "Training Time")
timeNewTime = TimeValue(strNewTime)

answer = DLookup("TrainingTimeId", "tblTrainingTime", "TrainingTime = #" & Format(timeNewTime, "hh:nn") & "#")

I enter 8:00 into the Inputbox

I get a run time error 94. Invalid use of Null in the line of code: answer = DLookup...................

A record does exist in tblTrainingTime
The properties of field TrainingTime in tblTrainingTime is Date Type = Date/Time and format is Short Time

What am I doing wrong?

Posted by: cheekybuddha Jun 24 2019, 06:53 AM

>> What am I doing wrong? <<
You are not understanding how dates/times are stored in Access (or by computers in general).

Dates and times are stored as a number representing the number of days after a base date. In the case of Access, this is 30th December 1899.

The whole portion of the number (integer) represents days, and the fraction portion represents fractions of days, ie time.

All you see in your tables/queries etc are formatted representations of these numbers.

As a test, even though you think you have just stored times in your table, try running the following query and let us know what sort of results are returned:

  Format(TrainingTime, "yyyy-mm-dd hh:nn:ss")
FROM tblTrainingTime;

My guess, is that there will be a date part as well as the time.

What I am interested in knowing is whether the date part is '1899-12-30', or perhaps the date that you entered the time in to the table.

Posted by: TimTDP Jun 24 2019, 07:12 AM


answer = DLookup("TrainingTime", "tblTrainingTime", "TrainingTimeId = 598548")

answer = 0. It should return 9:00am!

Attached database with table ( 19.33K ): 4

Posted by: cheekybuddha Jun 24 2019, 07:15 AM

What did running the query I suggested return?

I can't download stuff here at work

Posted by: TimTDP Jun 24 2019, 07:17 AM

TrainingTimeId Expr1001
598539 1899-12-30 06:00:00
598542 1899-12-30 06:00:00
598543 1899-12-30 06:30:00
598544 1899-12-30 07:00:00
598545 1899-12-30 07:30:00
598546 1899-12-30 08:00:00
598547 1899-12-30 08:30:00
598548 1899-12-30 09:00:00
598549 1899-12-30 09:30:00
598550 1899-12-30 10:00:00
598551 1899-12-30 10:30:00

Posted by: cheekybuddha Jun 24 2019, 07:26 AM


First, try simplifying:

Dim strNewTime As String
Dim answer As Variant    ' <-- NOTE: I HAVE CHANGED THIS in case no matching result is found

strNewTime = InputBox("Enter training time as hh:mm", "Training Time")
If IsDate(strNewTime) Then
  answer = DLookup("TrainingTimeId", "tblTrainingTime", "TrainingTime = #" & strNewTime & "#")
End If

Posted by: Phil_cattivocarattere Jun 24 2019, 07:29 AM

answer = 0. It should return 9:00am!

If you write in immediate window the DLookup only you will get the right value.
The problem is when you assign it to a variable declared as (integer) long. Time is saved as the decimal part of a double datatype.

You will get 0.374. When you assign that value to a long variable you get... 0.
This is only to explain that strange behaviour. Follow cheekybuddha suggestion to solve everything.

Posted by: TimTDP Jun 24 2019, 09:47 AM

Thanks Guys
I was only saving the time portion. I think I will add the date as well. Then I won't have this problem

Posted by: datAdrenaline Jun 25 2019, 02:02 PM

Coerce both sides to a formatted string ... of course you lose the advantage of indexes, but ... you gain the assurance of knowing you are comparing only the time portions.

answer = DLookup("TrainingTimeId", "tblTrainingTime", "Format(TrainingTime,'hh:nn') = '" & Format(timeNewTime, "hh:nn") & "'")

Posted by: TimTDP Jun 26 2019, 01:57 AM

Thank you

Posted by: gemmathehusky Jul 15 2019, 06:32 AM

Just a note - you need to be careful about using absolute times for this sort of thing. A time is a fractional part of a day. Many times cannot be represented with 100% access in binary. 8:00am is a third of a day, and cannot be represented in either binary or decimal. (but obviously can be in base 3, although that doesn't help). Therefore if you try to test equality to 8:00am it may or may not work.

If you start mixing, doubles, singles and currency data types, you are almost certainly going to get differences, because all 3 will (probably) give slightly different values.

Posted by: gemmathehusky Jul 15 2019, 06:37 AM


surely 9:00 am should return 0.375, not 0.374. 9 hours is 3/8 of a day, and clearly is perfectly representable in binary. (a quarter 0.01) plus an eighth (0.001) so it should be 0.011
if you really get 0.374 there's some round off error somewhere.