date range query

[flumpuk]

Hi

My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created a table in Access with four fields
Date Location Number of Adult Visitors Number of
Chikd Visitors
I have created queries to tell me how many occurances of Location and
total and average number of visitors there are in the table and this
works fine for reporting purposes.
We keep data on a month by month basis
It has been decided that we will keep all data in a single database
from now on.
What I need to do is rewrite the queries so that I can query a date
range rather then the entire table.
How do I do this?
brian

[Rick Brandt]

flumpuk wrote:

Hi

My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created a table in Access with four fields
Date Location Number of Adult Visitors Number of
Chikd Visitors
I have created queries to tell me how many occurances of Location and
total and average number of visitors there are in the table and this
works fine for reporting purposes.
We keep data on a month by month basis
It has been decided that we will keep all data in a single database
from now on.
What I need to do is rewrite the queries so that I can query a date
range rather then the entire table.
How do I do this?

First change the name of that field to VisitDate (Date is a reserverd word).
The use a query with criteria on the VisitDate field like...

Field VisitDate
Criteria >=#2007-05-01# AND <#2007-06-01#

That will return all records for May and it will work whether VisitDate contains
date only (all times set to midnight) or date and time. If you store all
midnight times and are confident that this won't change then you can also use...

Field VisitDate
Criteria BETWEEN #2007-05-01# AND #2007-05-31#

I used the ISO format for my date literals because it is best to use a
non-abiguous format. If you don't use a non-ambiguous format then you must use
US format m/d/yy or m/d/yyyy.

You could of course have the query pull the criteria from an open form where you
could specify the date range...

Field VisitDate
Criteria BETWEEN Forms!NameOfForm!StartDate AND Forms!NameOfForm!EndDate

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com