query on records based on time interval

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xiaodan86@hotmail.com - 20 Mar 2007 07:46 GMT

If I have a table something like this:

startdate | stime | endate | etime | tool
3/3/07 | 8:47 | 3/4/07 | 00:30 | A
3/4/07 | 3:57 | 3/4/07 |4:30 | B
3/4/07 |8:21 | 3/4/07 |9:20 | C

Currently I have a form which use to open dailyrecord.
I have one textbox([date]) for enter date and a button to view.
Is it possible when I wan to open a new form with all the records on
3/4/07
it will only give me the records from 3/3/07 7:00 till 3/4/07 7:00
(thus, only show tool A and tool B)

my currently event procedure for the button is below:

Dim strSQL As String
Dim strWhere As String

If Not IsNull(date) Then
strWhere = strWhere & " AND startdate = " & "#" & _
date & "#"

If Not IsNull(strWhere) Then
strSQL = Mid$(strWhere, 6)
End If

strForm = "daily1"
DoCmd.openform strForm, , , strSQL

Anything can be change on the code in order to search the records base
on time interval?
Thanks =)

Reply to this Message

John W. Vinson - 20 Mar 2007 18:03 GMT

>If I have a table something like this:
>
[quoted text clipped - 9 lines]
>it will only give me the records from 3/3/07 7:00 till 3/4/07 7:00
>(thus, only show tool A and tool B)

This would be a LOT easier if you combined the date and time fields into a
StartTime and EndTime field. An Access Date/Time value is actually a number, a
count of days and fractions of a day since midnight, December 30, 1899.
Therefore a pure time value - like your 00:30 - is actually stored as
0.0283333333 or (to display it another way) #12/30/1899 00:30:00#.

If you combine the fields, you can get your query very simply with a criterion

BETWEEN [Enter start date and time] AND DateAdd("d", 1, [Enter start date and
time] )

Don't use Date as the name of the textbox - it refers to the Date() function
which returns today's date.

You can still do this (albeit less efficiently) by applying this criterion to
a calculated field: put

StartTime: [startdate] + NZ([stime])

in a vacant field cell.

John W. Vinson [MVP]

Reply to this Message