By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Dlookup with time criteria

reginaldmerritt
100+
P: 201
I have a table which just lists time slots. Every 15 mins from 9:00am - 5:00pm. The format of [Time] in TBTime is 'Medium Time'.

e.g. TBTime
TimeID___Time
___1_____9:00am
___2_____9:15am
___3_____9:30am

I want to Dlookup the [TimeID] based on the [Time].

When I used the code:
Expand|Select|Wrap|Line Numbers
  1. DLookup("[TimeID]", "TBTime", "[Time] = #" & Me.NextAvaliablePickupTime & "#")
Bazaarly this will result in a null answer unless the time is dividable by 3, i.e. if the time is 3:00am,6:00am,9:00am or 12:00am the Dlookup statement works, otherwise it gives in a null result.

I tried putting the TBtime into a query using 'FormatedDate: Cdate([Time])' and changing the code to:
Expand|Select|Wrap|Line Numbers
  1. DLookup "[TimeID]", "QYFormatedTime", "[FormatedTime] = #" & Me.NextAvaliablePickupTime & "#")
But this didn't make any difference. I don't understand what is happening and I don't know what else to try, any ideas would be greatly appreciated.
Aug 23 '12 #1
Share this Question
Share on Google+
21 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub GetTime()
  2.     Dim dtTime As Date
  3.     dtTime = DLookup("[Time]", "TBTime", "[TimeID] = " & 2)
  4.     Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
  5.         DLookup("[TimeID]", "TBTime", "[Time] = #" & dtTime & "#")
  6.     dtTime = Time
  7.     Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
  8.         DLookup("[TimeID]", "TBTime", "[Time] > #" & dtTime & "#")
  9.     dtTime = Me.NextAvaliablePickupTime
  10.     Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
  11.         DLookup("[TimeID]", "TBTime", "[Time] > #" & dtTime & "#")
  12.     Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
  13.         DLookup("[TimeID]", "TBTime", "[Time] < #" & dtTime & "#")
  14.     Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss"), _
  15.         DLookup("[TimeID]", "TBTime", "[Time] = #" & dtTime & "#")
  16. End Sub
  17.  
My thought is that somewhere, your NextAvailablePickupTime 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:

Expand|Select|Wrap|Line Numbers
  1. Debug.Print Time
  2.  
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.
Aug 23 '12 #2

reginaldmerritt
100+
P: 201
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.NextAvaliablePickupTime]
30 Dec 1899, 09:15:00_____1_____[TIME < Me.NextAvaliablePickupTime]
30 Dec 1899, 09:15:00_____Null___[TIME = Me.NextAvaliablePickupTime]

the last 3 use Me.NextAvaliablePickupTime, even more bizzarly using the '>' sign seems to work and using '=' or '<' does not ????????
Aug 23 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
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 NextAvaliablePickupTime, but this would eliminate any format issues with the time.
Aug 23 '12 #4

reginaldmerritt
100+
P: 201
Thats a good shout twinnyfo, NextAvaliablePickupTime is a combobox to select Time but it is unbound. I can't seem to use NextAvaliablePickupTime.Column(0), i think this is because it is unbound.
Aug 23 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
If "NextAvaliablePickupTime 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...
Aug 23 '12 #6

reginaldmerritt
100+
P: 201
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.

Expand|Select|Wrap|Line Numbers
  1. dtTime = #12:00:00 AM#
  2. Debug.Print Format(dtTime, "dd mmm yyyy, hh:mm:ss")
  3. For i = 1 To 96
  4. Debug.Print "DLookup [TimeSlot] =" & dtTime & " " & DLookup("[TimeID]", "TBTime", "[TimeSlot] = #" & dtTime & "#")
  5. dtTime = DateAdd("n", 15, dtTime)
  6. Next
  7.  
  8. This is the result
  9. 30 Dec 1899, 00:00:00
  10. DLookup [TimeSlot] =12:00:00 AM__1
  11. DLookup [TimeSlot] =12:15:00 AM__2
  12. DLookup [TimeSlot] =12:30:00 AM__4
  13. DLookup [TimeSlot] =12:45:00 AM__5
  14. DLookup [TimeSlot] =01:00:00 AM 
  15. DLookup [TimeSlot] =01:15:00 AM 
  16. DLookup [TimeSlot] =01:30:00 AM__8
  17. DLookup [TimeSlot] =01:45:00 AM 
  18. DLookup [TimeSlot] =02:00:00 AM 
  19. DLookup [TimeSlot] =02:15:00 AM__11
  20. DLookup [TimeSlot] =02:30:00 AM 
  21. DLookup [TimeSlot] =02:45:00 AM 
  22. DLookup [TimeSlot] =03:00:00 AM__14
  23. DLookup [TimeSlot] =03:15:00 AM 
  24. DLookup [TimeSlot] =03:30:00 AM 
  25. DLookup [TimeSlot] =03:45:00 AM__17
  26. DLookup [TimeSlot] =04:00:00 AM 
  27. DLookup [TimeSlot] =04:15:00 AM 
  28. DLookup [TimeSlot] =04:30:00 AM__20
  29. DLookup [TimeSlot] =04:45:00 AM 
  30. DLookup [TimeSlot] =05:00:00 AM 
  31. DLookup [TimeSlot] =05:15:00 AM__23
  32. DLookup [TimeSlot] =05:30:00 AM 
  33. DLookup [TimeSlot] =05:45:00 AM 
  34. DLookup [TimeSlot] =06:00:00 AM__26
  35. DLookup [TimeSlot] =06:15:00 AM 
  36. DLookup [TimeSlot] =06:30:00 AM 
  37. DLookup [TimeSlot] =06:45:00 AM__29
  38. DLookup [TimeSlot] =07:00:00 AM 
  39. DLookup [TimeSlot] =07:15:00 AM 
  40. DLookup [TimeSlot] =07:30:00 AM__32
  41. DLookup [TimeSlot] =07:45:00 AM 
  42. DLookup [TimeSlot] =08:00:00 AM 
  43. DLookup [TimeSlot] =08:15:00 AM__35
  44. DLookup [TimeSlot] =08:30:00 AM 
  45. DLookup [TimeSlot] =08:45:00 AM 
  46. DLookup [TimeSlot] =09:00:00 AM__38
  47. DLookup [TimeSlot] =09:15:00 AM 
  48. DLookup [TimeSlot] =09:30:00 AM 
  49. DLookup [TimeSlot] =09:45:00 AM__41
  50. DLookup [TimeSlot] =10:00:00 AM 
  51. DLookup [TimeSlot] =10:15:00 AM 
  52. DLookup [TimeSlot] =10:30:00 AM__44
  53. DLookup [TimeSlot] =10:45:00 AM 
  54. DLookup [TimeSlot] =11:00:00 AM 
  55. DLookup [TimeSlot] =11:15:00 AM__47
  56. DLookup [TimeSlot] =11:30:00 AM 
  57. DLookup [TimeSlot] =11:45:00 AM 
  58. DLookup [TimeSlot] =12:00:00 PM__50
  59. DLookup [TimeSlot] =12:15:00 PM 
  60. DLookup [TimeSlot] =12:30:00 PM 
  61. DLookup [TimeSlot] =12:45:00 PM__53
  62. DLookup [TimeSlot] =01:00:00 PM 
  63. DLookup [TimeSlot] =01:15:00 PM 
  64. DLookup [TimeSlot] =01:30:00 PM__56
  65. DLookup [TimeSlot] =01:45:00 PM 
  66. DLookup [TimeSlot] =02:00:00 PM 
  67. DLookup [TimeSlot] =02:15:00 PM__59
  68. DLookup [TimeSlot] =02:30:00 PM 
  69. DLookup [TimeSlot] =02:45:00 PM 
  70. DLookup [TimeSlot] =03:00:00 PM__62
  71. DLookup [TimeSlot] =03:15:00 PM 
  72. DLookup [TimeSlot] =03:30:00 PM 
  73. DLookup [TimeSlot] =03:45:00 PM__65
  74. DLookup [TimeSlot] =04:00:00 PM 
  75. DLookup [TimeSlot] =04:15:00 PM 
  76. DLookup [TimeSlot] =04:30:00 PM__68
  77. DLookup [TimeSlot] =04:45:00 PM 
  78. DLookup [TimeSlot] =05:00:00 PM 
  79. DLookup [TimeSlot] =05:15:00 PM__71
  80. DLookup [TimeSlot] =05:30:00 PM 
  81. DLookup [TimeSlot] =05:45:00 PM 
  82. DLookup [TimeSlot] =06:00:00 PM__74
  83. DLookup [TimeSlot] =06:15:00 PM 
  84. DLookup [TimeSlot] =06:30:00 PM 
  85. DLookup [TimeSlot] =06:45:00 PM__77
  86. DLookup [TimeSlot] =07:00:00 PM 
  87. DLookup [TimeSlot] =07:15:00 PM 
  88. DLookup [TimeSlot] =07:30:00 PM__80
  89. DLookup [TimeSlot] =07:45:00 PM 
  90. DLookup [TimeSlot] =08:00:00 PM 
  91. DLookup [TimeSlot] =08:15:00 PM__83
  92. DLookup [TimeSlot] =08:30:00 PM 
  93. DLookup [TimeSlot] =08:45:00 PM 
  94. DLookup [TimeSlot] =09:00:00 PM__86
  95. DLookup [TimeSlot] =09:15:00 PM 
  96. DLookup [TimeSlot] =09:30:00 PM 
  97. DLookup [TimeSlot] =09:45:00 PM__89
  98. DLookup [TimeSlot] =10:00:00 PM 
  99. DLookup [TimeSlot] =10:15:00 PM 
  100. DLookup [TimeSlot] =10:30:00 PM__92
  101. DLookup [TimeSlot] =10:45:00 PM 
  102. DLookup [TimeSlot] =11:00:00 PM 
  103. DLookup [TimeSlot] =11:15:00 PM__95
  104. DLookup [TimeSlot] =11:30:00 PM 
  105. DLookup [TimeSlot] =11:45:00 PM
  106.  
Expand|Select|Wrap|Line Numbers
  1. Dlookup using Time Criteria seems to only work on the following times:
  2. 12:00:00 AM
  3. 12:15:00 AM
  4. 12:30:00 AM
  5. 12:45:00 AM
  6. 01:30:00 AM
  7. 02:15:00 AM
  8. 03:00:00 AM
  9. 03:45:00 AM
  10. 04:30:00 AM
  11. 05:15:00 AM
  12. 06:00:00 AM
  13. 06:45:00 AM
  14. 07:30:00 AM
  15. 08:15:00 AM
  16. 09:00:00 AM
  17. 09:45:00 AM  
  18. 10:30:00 AM 
  19. 11:15:00 AM 
  20. 12:00:00 PM 
  21. 12:45:00 PM 
  22. 01:30:00 PM 
  23. 02:15:00 PM 
  24. 03:00:00 PM 
  25. 03:45:00 PM 
  26. 04:30:00 PM 
  27. 05:15:00 PM 
  28. 06:00:00 PM 
  29. 06:45:00 PM 
  30. 07:30:00 PM 
  31. 08:15:00 PM 
  32. 09:00:00 PM 
  33. 09:45:00 PM 
  34. 10:30:00 PM 
  35. 11:15:00 PM 
  36.  
Starts off a bit random and then every 3rd time slot works?
Aug 23 '12 #7

reginaldmerritt
100+
P: 201
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.
Aug 23 '12 #8

twinnyfo
Expert Mod 2.5K+
P: 3,284
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
File Type: zip Time.zip (70.2 KB, 102 views)
Aug 23 '12 #9

reginaldmerritt
100+
P: 201
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.
Aug 23 '12 #10

twinnyfo
Expert Mod 2.5K+
P: 3,284
True, true, true. I hope we expanded your tool kit this morning....
Aug 23 '12 #11

reginaldmerritt
100+
P: 201
Actually twinnyfo there is another part of my program that time is not taken from a combobox so back to the drawing board
Aug 23 '12 #12

reginaldmerritt
100+
P: 201
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:
Expand|Select|Wrap|Line Numbers
  1. Dim rsTime As Recordset
  2. Dim dtTime As Date
  3.  
  4. Set rsTime = CurrentDb.OpenRecordset("TBTime", dbOpenDynaset)
  5.  
  6. rsTime.MoveFirst
  7. For i = 1 To 96
  8.     If CStr(rsTime!TimeSlot) = CStr(Me.PickupTime) Then
  9.         MsgBox ("Eureka! TimeID = " & rsTime!TimeID)
  10.     End If
  11. rsTime.MoveNext
  12. Next
  13.  
  14. rsTime.Close
  15. Set rsTime = Nothing
Aug 23 '12 #13

reginaldmerritt
100+
P: 201
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 ;)
Aug 23 '12 #14

NeoPa
Expert Mod 15k+
P: 31,492
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.
Aug 23 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
Here's an alternate method.
Expand|Select|Wrap|Line Numbers
  1. (Hour(dateField) - 9) * 4 + 1 + Minute(dateField) \ 15
It should only be used if you're sure you won't be changing those keys or adding/removing keys.
Aug 23 '12 #16

NeoPa
Expert Mod 15k+
P: 31,492
Relevant to the thread, but essentially for Rabbit's eyes only - and I only comment at all because I know the level you work at :-

Firstly, and the more humourous of the points - I think you mean an alternative suggestion ;-)

More SQL related - your suggestion can certainly work, but assuming a decent understanding of SQL then using Date/Time literals would generally be a preferable approach. Many struggle with doing that correctly, so an alternative suggestion makes sense, but I believe the most appropriate solution should certainly be included in here, regardless of whether or not the OP appreciates the point of it.
Aug 23 '12 #17

Rabbit
Expert Mod 10K+
P: 12,366
@NeoPa, Indeed I do mean alternative suggestion lol. I posted it mostly to show that there's more than one way to skin a cat.

On a different note, I do have to question the purpose of the time key and whether it's needed at all.
Aug 23 '12 #18

reginaldmerritt
100+
P: 201
Doh! thought I was being really cleaver but fair enough. Thank you for your post and links NeoPa I have already added the pages to my favourites. Rabbit thanks for your alternative.
Aug 23 '12 #19

NeoPa
Expert Mod 15k+
P: 31,492
You're welcome Reginald. Remember though, my post was not simply added to direct you on how better to post in future, it was also an offer of more help here if you can post the details in a way that gives us something more concrete to work with.

Of course, you may already consider you have what you need for this case, in which case we're all happy :-)
Aug 23 '12 #20

P: 1
I have had exactly this problem, and came across this old thread when looking for a solution. In the end I figured out a way that worked, so am posting it here in case it is useful for anyone else.

Using the DateDiff function to compare times works (have the interval set to Seconds), eg:
Expand|Select|Wrap|Line Numbers
  1. DLookup("[TimeID]", "TBTime", "DateDiff('s',[Time], #" & Me.NextAvaliablePickupTime & "#)")
4 Weeks Ago #21

NeoPa
Expert Mod 15k+
P: 31,492
Hi Tom.

First - Welcome to Bytes.com.

My comment on your suggestion is that, while it may work, when you have to start using Domain functions within your SQL then it generally means (or should at least) that you've exhausted a more natural approach to the problem.

Most of the power of working in SQL is in the incredible ways it can manage and use data from various disparate sources in an efficient manner. It can do this because of what it knows of the design of the tables involved. Once you start using Domain functions within that then that part is like a separate engine for producing a result. The two processes, even though in many cases they are the same actual software & logic producing the results, are disconnected from each other so have no way of working together efficiently.

In small data situations this is an inefficiency overhead that can be happily overlooked, but in other situations can cause actual, real-world, problems.

As a general guideline I always say avoid Domain functions in your SQL where you can. Immediate Pane work and often in general VBA too, don't give it a second thought, but in SQL within production systems be aware of the above.

To finish I'd like to stress how much we appreciate your taking the time and effort to register in order to share your knowledge and understanding. I hope my comments above have been illuminating but the important point here is that you posted to help which is always appreciated.

All the best -Ade.
4 Weeks Ago #22

Post your reply

Sign in to post your reply or Sign up for a free account.