[X]My Assistant

[TimTDP]

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

CODE

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?

[cheekybuddha]

>> 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:

CODE

SELECT
  TrainingTimeId,
  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.

[TimTDP]

Thanks

CODE

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

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

Attached database with table

Attached File(s)

 Database3.zip ( 19.33K )Number of downloads: 6

 

[cheekybuddha]

What did running the query I suggested return?

I can't download stuff here at work

[TimTDP]

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

[cheekybuddha]

OK,

First, try simplifying:

CODE

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

[Phil_cattivocara...]

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.
Try

CODE

CDbl(TimeValue("9:00"))

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.

[TimTDP]

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

[datAdrenaline]

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.

CODE

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

[TimTDP]

Thank you

[gemmathehusky]

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.

[gemmathehusky]

@phil

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.