Dlookup with time criteria

**twinnyfo** · Aug 23 '12, 12:42 PM

Reginald,

I can't explain what is happening, but try this. Create this public sub in a module and watch what happened when you run it from the immediate window.

Code:

Public Sub GetTime()
    Dim dtTime As Date
    dtTime = DLookup("[Time]", "TBTime", "[TimeID] = " & 2)
    Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
        DLookup("[TimeID]", "TBTime", "[Time] = #" & dtTime & "#")
    dtTime = Time
    Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
        DLookup("[TimeID]", "TBTime", "[Time] > #" & dtTime & "#")
    dtTime = Me.NextAvaliablePickupTime
    Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
        DLookup("[TimeID]", "TBTime", "[Time] > #" & dtTime & "#")
    Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
        DLookup("[TimeID]", "TBTime", "[Time] < #" & dtTime & "#")
    Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
        DLookup("[TimeID]", "TBTime", "[Time] = #" & dtTime & "#")
End Sub

My thought is that somewhere, your NextAvailablePi ckupTime is not in exactly the same format as your TBTime. The TBTime.Time field may be saving the time without a date and your nextAvailable PickUpTime might have a date included.

Also, it is also very wise to never have a field named "Time", as this could confuse your db, as Time is a reserved word. Try typing this is your immediate window:

Code:

Debug.Print Time

and you will see what I mean. This goes for other field names like "Name". This, however, is not what is causing problems within your code.

Let me know if this short code shows you anything revealing.

**reginaldmerritt** · Aug 23 '12, 01:07 PM

I thought if Time was a reserved word Access wouldn't let me use it. But I created duplicate table and renamed the field to [TimeSlot] but this made no difference.

I understand where your coming from, could be that a date is being stored but with your debug code i can see that a date is not being stored.

This is the results
30 Dec 1899, 00:15:00_____2_ ____[TimeID = 2]
30 Dec 1899, 13:53:11_____58 ____[TIME = Time()]
30 Dec 1899, 09:15:00_____39 ____[TIME > Me.NextAvaliabl ePickupTime]
30 Dec 1899, 09:15:00_____1_ ____[TIME < Me.NextAvaliabl ePickupTime]
30 Dec 1899, 09:15:00_____Nu ll___[TIME = Me.NextAvaliabl ePickupTime]

the last 3 use Me.NextAvaliabl ePickupTime, even more bizzarly using the '>' sign seems to work and using '=' or '<' does not ????????

**twinnyfo** · Aug 23 '12, 01:15 PM

I just thought of something.....

If you have your TBTime table, with and index for each time, would it be possible to have a combo box on your form, using the TBTable as the row source for that combo box? Then, someone selects a time from the combo box, and the combo box uses the index, rather than the time.

I'm not exactly sure how the user inputs the NextAvaliablePi ckupTime, but this would eliminate any format issues with the time.

**reginaldmerritt** · Aug 23 '12, 01:23 PM

Thats a good shout twinnyfo, NextAvaliablePi ckupTime is a combobox to select Time but it is unbound. I can't seem to use NextAvaliablePi ckupTime.Column (0), i think this is because it is unbound.

**twinnyfo** · Aug 23 '12, 01:29 PM

If "NextAvaliableP ickupTime is a combobox to select Time but it is unbound" then how does one select a time? I would play around with this control a bit. If you establish that combo box with two columns, the bound column is the index, and only display column two (the Time), all should be fine. Then, you already know the index number and don't need the DLookup...

**reginaldmerritt** · Aug 23 '12, 01:33 PM

This is very strange. I thought i would run though all the TimeSlots to see if there was a pattern to those that worked within Dlookup and those that did not.

Code:

dtTime = #12:00:00 AM#
Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss")
For i = 1 To 96
Debug.Print "DLookup [TimeSlot] =" & dtTime & " " & DLookup("[TimeID]", "TBTime", "[TimeSlot] = #" & dtTime & "#")
dtTime = DateAdd("n", 15, dtTime)
Next

This is the result
30 Dec 1899, 00:00:00
DLookup [TimeSlot] =12:00:00 AM__1
DLookup [TimeSlot] =12:15:00 AM__2
DLookup [TimeSlot] =12:30:00 AM__4
DLookup [TimeSlot] =12:45:00 AM__5
DLookup [TimeSlot] =01:00:00 AM 
DLookup [TimeSlot] =01:15:00 AM 
DLookup [TimeSlot] =01:30:00 AM__8
DLookup [TimeSlot] =01:45:00 AM 
DLookup [TimeSlot] =02:00:00 AM 
DLookup [TimeSlot] =02:15:00 AM__11
DLookup [TimeSlot] =02:30:00 AM 
DLookup [TimeSlot] =02:45:00 AM 
DLookup [TimeSlot] =03:00:00 AM__14
DLookup [TimeSlot] =03:15:00 AM 
DLookup [TimeSlot] =03:30:00 AM 
DLookup [TimeSlot] =03:45:00 AM__17
DLookup [TimeSlot] =04:00:00 AM 
DLookup [TimeSlot] =04:15:00 AM 
DLookup [TimeSlot] =04:30:00 AM__20
DLookup [TimeSlot] =04:45:00 AM 
DLookup [TimeSlot] =05:00:00 AM 
DLookup [TimeSlot] =05:15:00 AM__23
DLookup [TimeSlot] =05:30:00 AM 
DLookup [TimeSlot] =05:45:00 AM 
DLookup [TimeSlot] =06:00:00 AM__26
DLookup [TimeSlot] =06:15:00 AM 
DLookup [TimeSlot] =06:30:00 AM 
DLookup [TimeSlot] =06:45:00 AM__29
DLookup [TimeSlot] =07:00:00 AM 
DLookup [TimeSlot] =07:15:00 AM 
DLookup [TimeSlot] =07:30:00 AM__32
DLookup [TimeSlot] =07:45:00 AM 
DLookup [TimeSlot] =08:00:00 AM 
DLookup [TimeSlot] =08:15:00 AM__35
DLookup [TimeSlot] =08:30:00 AM 
DLookup [TimeSlot] =08:45:00 AM 
DLookup [TimeSlot] =09:00:00 AM__38
DLookup [TimeSlot] =09:15:00 AM 
DLookup [TimeSlot] =09:30:00 AM 
DLookup [TimeSlot] =09:45:00 AM__41
DLookup [TimeSlot] =10:00:00 AM 
DLookup [TimeSlot] =10:15:00 AM 
DLookup [TimeSlot] =10:30:00 AM__44
DLookup [TimeSlot] =10:45:00 AM 
DLookup [TimeSlot] =11:00:00 AM 
DLookup [TimeSlot] =11:15:00 AM__47
DLookup [TimeSlot] =11:30:00 AM 
DLookup [TimeSlot] =11:45:00 AM 
DLookup [TimeSlot] =12:00:00 PM__50
DLookup [TimeSlot] =12:15:00 PM 
DLookup [TimeSlot] =12:30:00 PM 
DLookup [TimeSlot] =12:45:00 PM__53
DLookup [TimeSlot] =01:00:00 PM 
DLookup [TimeSlot] =01:15:00 PM 
DLookup [TimeSlot] =01:30:00 PM__56
DLookup [TimeSlot] =01:45:00 PM 
DLookup [TimeSlot] =02:00:00 PM 
DLookup [TimeSlot] =02:15:00 PM__59
DLookup [TimeSlot] =02:30:00 PM 
DLookup [TimeSlot] =02:45:00 PM 
DLookup [TimeSlot] =03:00:00 PM__62
DLookup [TimeSlot] =03:15:00 PM 
DLookup [TimeSlot] =03:30:00 PM 
DLookup [TimeSlot] =03:45:00 PM__65
DLookup [TimeSlot] =04:00:00 PM 
DLookup [TimeSlot] =04:15:00 PM 
DLookup [TimeSlot] =04:30:00 PM__68
DLookup [TimeSlot] =04:45:00 PM 
DLookup [TimeSlot] =05:00:00 PM 
DLookup [TimeSlot] =05:15:00 PM__71
DLookup [TimeSlot] =05:30:00 PM 
DLookup [TimeSlot] =05:45:00 PM 
DLookup [TimeSlot] =06:00:00 PM__74
DLookup [TimeSlot] =06:15:00 PM 
DLookup [TimeSlot] =06:30:00 PM 
DLookup [TimeSlot] =06:45:00 PM__77
DLookup [TimeSlot] =07:00:00 PM 
DLookup [TimeSlot] =07:15:00 PM 
DLookup [TimeSlot] =07:30:00 PM__80
DLookup [TimeSlot] =07:45:00 PM 
DLookup [TimeSlot] =08:00:00 PM 
DLookup [TimeSlot] =08:15:00 PM__83
DLookup [TimeSlot] =08:30:00 PM 
DLookup [TimeSlot] =08:45:00 PM 
DLookup [TimeSlot] =09:00:00 PM__86
DLookup [TimeSlot] =09:15:00 PM 
DLookup [TimeSlot] =09:30:00 PM 
DLookup [TimeSlot] =09:45:00 PM__89
DLookup [TimeSlot] =10:00:00 PM 
DLookup [TimeSlot] =10:15:00 PM 
DLookup [TimeSlot] =10:30:00 PM__92
DLookup [TimeSlot] =10:45:00 PM 
DLookup [TimeSlot] =11:00:00 PM 
DLookup [TimeSlot] =11:15:00 PM__95
DLookup [TimeSlot] =11:30:00 PM 
DLookup [TimeSlot] =11:45:00 PM

Code:

Dlookup using Time Criteria seems to only work on the following times:
12:00:00 AM
12:15:00 AM
12:30:00 AM
12:45:00 AM
01:30:00 AM
02:15:00 AM
03:00:00 AM
03:45:00 AM
04:30:00 AM
05:15:00 AM
06:00:00 AM
06:45:00 AM
07:30:00 AM
08:15:00 AM
09:00:00 AM
09:45:00 AM  
10:30:00 AM 
11:15:00 AM 
12:00:00 PM 
12:45:00 PM 
01:30:00 PM 
02:15:00 PM 
03:00:00 PM 
03:45:00 PM 
04:30:00 PM 
05:15:00 PM 
06:00:00 PM 
06:45:00 PM 
07:30:00 PM 
08:15:00 PM 
09:00:00 PM 
09:45:00 PM 
10:30:00 PM 
11:15:00 PM

Starts off a bit random and then every 3rd time slot works?

**reginaldmerritt** · Aug 23 '12, 01:38 PM

I mean it's unbound in the fact that the form itself is not accessing a table. All the controls are unbound.

Try creating a new form, add a combobox, add a rowsource and then try to use .column to select data from the combobox. Let me know if that works, maybe it's a setting i need to change.

**twinnyfo** · Aug 23 '12, 01:59 PM

Here is what I was describing. Open the PickupTimes form in the attachment.

You'll notice that the index is displayed whenever you select a different time.

Attached Files

Time.zip (70.2 KB, 184 views)

**reginaldmerritt** · Aug 23 '12, 02:08 PM

Great, thanks. So you don't have to use .column to refer to a combobox. That will work.

I think the issue with Dlookup and Time could be the way access stores date and time as a number, with time being represented as a fraction. I tried using CDbl(dtTime) in Dlookup statement and this worked but not for all records in TBTime unfortunately.

**twinnyfo** · Aug 23 '12, 02:19 PM

True, true, true. I hope we expanded your tool kit this morning....

**reginaldmerritt** · Aug 23 '12, 02:21 PM

Actually twinnyfo there is another part of my program that time is not taken from a combobox so back to the drawing board

**reginaldmerritt** · Aug 23 '12, 02:55 PM

So the issue is with comparing to time values which appear to be the same but have a different serial fraction value.

When using Dlookup there was no way for me to format or change the [TimeSlot], I could only format the Combobox or Text Field. So Instead of using Dlookup I have used a recordset to get each [TimeSlot], change this to the same format as the Combobox or Text Field and then compare the two. I chose to convert [TimeSlot] and the Combobox or Text Field to string value but I guess you could use something else.

Here's my code:

Code:

Dim rsTime As Recordset
Dim dtTime As Date

Set rsTime = CurrentDb.OpenRecordset("TBTime", dbOpenDynaset)

rsTime.MoveFirst
For i = 1 To 96
    If CStr(rsTime!TimeSlot) = CStr(Me.PickupTime) Then
        MsgBox ("Eureka! TimeID = " & rsTime!TimeID)
    End If
rsTime.MoveNext
Next

rsTime.Close
Set rsTime = Nothing

**reginaldmerritt** · Aug 23 '12, 03:11 PM

twinnyfo many thanks for your time, it really helps to have someone to bounce idea of and help work though to a conclusion. We have definitely expanded my tool kit this mornin, thank you ;)

**NeoPa** · Aug 23 '12, 03:21 PM

I'm sorry Reginald, but we rarely allow OPs to assign their own posts as Best Answer (for fairly obvious reasons I would have thought). In exceptional circumstances where they come up with a really good answer that no-one else has contributed to, then maybe. In this case I'm afraid your post, while being sensible, actually doesn't answer the question and doesn't even give much indication that the underlying problem is properly understood.

Let's see if we can throw some light on that for you. The fundamental problem here appears to be related to understanding how SQL works and how to use it with Date/Time values. For this, a fundamental understanding of how they are stored (Totally disconnected from how they are represented when displayed.) is very important. Have a quick look first at Before Posting (VBA or SQL) Code as it explains how difficult it is (and generally pointless without all the supporting info anyway) to work with VBA code that creates SQL.

To work on your problem we'd need to see the actual SQL code (and that includes you too, as you won't get the suggestions if you don't perceive what's going on) as well as the data you're working with. How it's stored is important, rather than any format that may, or may not, be applied.

As a starter, you will find Literal DateTimes and Their Delimiters (#) very helpful. It's very important that the SQL is built correctly and that it correctly matches the value it's supposed to.

Dlookup with time criteria

Dlookup with time criteria

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment