UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup Using A Time As Criteria, Any Version    
 
   
TimTDP
post Jun 24 2019, 06:14 AM
Post#1



Posts: 1,090
Joined: 16-June 05



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?
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 06:53 AM
Post#2


UtterAccess VIP
Posts: 11,414
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
TimTDP
post Jun 24 2019, 07:12 AM
Post#3



Posts: 1,090
Joined: 16-June 05



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

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

Attached database with table
Attached File(s)
Attached File  Database3.zip ( 19.33K )Number of downloads: 1
 
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 07:15 AM
Post#4


UtterAccess VIP
Posts: 11,414
Joined: 6-December 03
From: Telegraph Hill


What did running the query I suggested return?

I can't download stuff here at work

--------------------


Regards,

David Marten
Go to the top of the page
 
TimTDP
post Jun 24 2019, 07:17 AM
Post#5



Posts: 1,090
Joined: 16-June 05



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
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 07:26 AM
Post#6


UtterAccess VIP
Posts: 11,414
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
Phil_cattivocara...
post Jun 24 2019, 07:29 AM
Post#7



Posts: 301
Joined: 2-April 18



QUOTE (TimTDP)
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.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
TimTDP
post Jun 24 2019, 09:47 AM
Post#8



Posts: 1,090
Joined: 16-June 05



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
Go to the top of the page
 
datAdrenaline
post Jun 25 2019, 02:02 PM
Post#9


UtterAccess Editor
Posts: 18,000
Joined: 4-December 03
From: Northern Virginia, USA


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") & "'")


--------------------
Brent Spaulding | datAdrenaline | Microsoft MVP 2007 - 2019
It's all very well to tell us to forgive our enemies; our enemies can never hurt us very much. But oh, what about forgiving our friends? - Willa Cather; As always - Pay it Forward!
Go to the top of the page
 
TimTDP
post Jun 26 2019, 01:57 AM
Post#10



Posts: 1,090
Joined: 16-June 05



Thank you
Go to the top of the page
 
gemmathehusky
post Yesterday, 06:32 AM
Post#11


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


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.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
gemmathehusky
post Yesterday, 06:37 AM
Post#12


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


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

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 10:16 AM