between time

[Guest]

I have a database that keeps track of student discipline. In it, I have a
tbldefaultvalues that contains information that doesn't change that I pull
drop down lists from. One field is called "Time" and I have populated it
with 15 minute increments from 6:00 AM to 8:00 PM (formatted as medium time).

I have a form that has a start date and end date and a start time (called
starttime) and end time (called endtime) that users can use the combo box to
pick the times.

I then have a query that should pull all of the referrals between the start
date and end date and the times. The date part works fine as it is has for
two years. I just changed the time from a single time (which works) to a
"between" time. Here is the sql I have set up:

SELECT[infractions archive].*
From [infractions archive]
where [time] between [forms]![search form]![starttime] and [forms]![search
form]![endtime] and
[date] Between [Forms]![Search form]![Start Date] And [Forms]![Search
form]![End Date]

UNION ALL select [infractions].*
From [infractions]
where [time] between [forms]![search form]![starttime] and [forms]![search
form]![endtime] and [date] Between [Forms]![search form]![Start Date] And
[Forms]![search form]![End Date]
ORDER BY [date];


This obviously pulls from a union of current infractions and archived
infractions. Again, it works if I use only one time (say the starttime
field) but the between is not working.

Any suggestions?

 

[Guest]

Eduman,

First off, you should NEVER use "TIME" and "DATE" as field names in your
database. These are Access Reserved words. Better field names would be
Infraction_Date and Infraction_Time, or better yet, a single field for
Infraction_Date_Time would be even better.

Whenever you work with dates and times, you need to remember that if the
value (in your case your form start and end dates) contains only a date
#7/19/2006# it actually represents #7/19/2006 00:00#, which means that if you
do a query with criteria "Between #7/16/2006# and #7/19/2006#, your query
will only return records where the date values are 7/16, 7/17, and 7/18.

How your query should be written depends on what records you actually want
your query to return. Using my dates above, do you want your query to give
you records where the values fall between #7/16/2006 08:00# and #7/19/2006
16:00# or do you want it to return records that fall between #08:00# and
#16:00# on dates that include #7/16/2006# through #7/19/2006#

Dale

 

[Guest]

Because the original entry form can be filled out at any time, not just the
time the incident occurs, I have a separate field for date and a separate
field for time. The time has to be one of the 15 minute increments that I
have prescribed (this is for another program mandated by the school system).

What I want to do is the second of your questions--find all infractions
between 8:00 am and 2:00 pm between the dates of 7/16/06 and 7/19/06. Should
I use military time or can I use AM and PM? I noticed you used military time.

Eduman,

First off, you should NEVER use "TIME" and "DATE" as field names in your
database. These are Access Reserved words. Better field names would be
Infraction_Date and Infraction_Time, or better yet, a single field for
Infraction_Date_Time would be even better.

Whenever you work with dates and times, you need to remember that if the
value (in your case your form start and end dates) contains only a date
#7/19/2006# it actually represents #7/19/2006 00:00#, which means that if you
do a query with criteria "Between #7/16/2006# and #7/19/2006#, your query
will only return records where the date values are 7/16, 7/17, and 7/18.

How your query should be written depends on what records you actually want
your query to return. Using my dates above, do you want your query to give
you records where the values fall between #7/16/2006 08:00# and #7/19/2006
16:00# or do you want it to return records that fall between #08:00# and
#16:00# on dates that include #7/16/2006# through #7/19/2006#

Dale

I have a database that keeps track of student discipline. In it, I have a
tbldefaultvalues that contains information that doesn't change that I pull
drop down lists from. One field is called "Time" and I have populated it
with 15 minute increments from 6:00 AM to 8:00 PM (formatted as medium time).

I have a form that has a start date and end date and a start time (called
starttime) and end time (called endtime) that users can use the combo box to
pick the times.

I then have a query that should pull all of the referrals between the start
date and end date and the times. The date part works fine as it is has for
two years. I just changed the time from a single time (which works) to a
"between" time. Here is the sql I have set up:

SELECT[infractions archive].*
From [infractions archive]
where [time] between [forms]![search form]![starttime] and [forms]![search
form]![endtime] and
[date] Between [Forms]![Search form]![Start Date] And [Forms]![Search
form]![End Date]

UNION ALL select [infractions].*
From [infractions]
where [time] between [forms]![search form]![starttime] and [forms]![search
form]![endtime] and [date] Between [Forms]![search form]![Start Date] And
[Forms]![search form]![End Date]
ORDER BY [date];


This obviously pulls from a union of current infractions and archived
infractions. Again, it works if I use only one time (say the starttime
field) but the between is not working.

Any suggestions?