467,146 Members | 1,029 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

date range query

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

May 13 '07 #1
  • viewed: 8794
Share:
1 Reply
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

May 13 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by mikevde | last post: by
2 posts views Thread by sixdeuce62 | last post: by
3 posts views Thread by RoadRunner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.