INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using Between And date selector in Access query

Using Between And date selector in Access query

(OP)
Hi, I have 28 separate queries that need to be run and each one has a need to return data between two dates so my queries use the "Between [Start date] And [End date]" criteria. However this means that with 28 queries I have to manually type in the same start date and same end date 28 times which is not very productive.
Is it possible to have a table that simply stores two dates, a Start Date and an End Date, that can be updated before running the 28 queries and for the queries to use these two dates as the criteria for the start and end dates?
If it is possible than I can put all 28 queries into a Macro and just let it run through them all knowing that each one is picking up the same Start Date and End date and have the Macro drop them all into one location.
Thanks in advance,
Clive

RE: Using Between And date selector in Access query

Is there any consistency in your Start Date and End Date?
What I mean is (for example): is the Start Date a today's date and End Date a date 2.5 weeks from today?
Or are the Start Date and End Date totally random and could be any dates?

In any event, you could put all your queries in one macro (one simple program) and ask for Start Date and End Date just once at the beginning and use the 2 dates all thru your macro.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Using Between And date selector in Access query

(OP)
Thanks for the reply Andy, although writing a Macro for this is a bit beyond me. I have 28 individual Aceess queries, I could use the Access Macro tool and run all 28 from here rather than running them one-by-one, but it's the date bit I'm stuck on. The dates will always be "the last seven days" (as long as I am at work & haven't taken any time off) but I don't want to hard code that in to the queries in case I'm off and have to run it when I'm back in, in which case the last 7 days would be wrong.

Each query has one date field on which I want to retrieve the last seven days data. When I started this there was only 1 query & it was simple to use the "Between [date] And [date]" criteria; now there are 28 (7 queries on 7 different tables for 4 different reasons).
I thought I had an easy way round it as I already had a table with every date in it from 2010 to 2050 and a Make Table query using the "Between [date] And [date]" criteria would have given me a little table with the previous 7 days. That table could then be used in each query joined to the date field but, the source tables are storing the date and time in them not just the dd/mm/yyyy that I want & I'm assuming it is this disparity that is preventing the join from working. If you can answer this isue then although not a programmers answer, it would answer my needs.
Thanks,
Clive

RE: Using Between And date selector in Access query

If that would be my task: run those 28 queries from last 7 days and keep the results some way (report, csv file, Excel, ...), I would do it my lazy way:
In the queries I would use "Between [date] - 7 And [date]" so I get the last seven days of data any time it is run.
So you say: What about "in case I'm off and have to run it when I'm back in"
I would create a little app and schedule it to run every day (or Mon-Fri) at whatever time it needs to run (at midnight?). This way I don't even have to be at work to do my task. smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Using Between And date selector in Access query

(OP)
Thanks Andy, I guess I was trying to find a clever solution but really, if I just use the [date]-7 route, it would solve the vast majority of issues as I am going to be at work way more than not and can deal with the exceptions as necessary as I can't schedule it to run, that's not a possibility.
Thanks
Clive

RE: Using Between And date selector in Access query

I have used a "parameter" table with only one record and a couple date fields. The key is to NEVER allow deviation from the one record or your results my vary wildly.

You simply add the table to the query and don't join it to any field in any table. Add
   Between FromDate and ToDate 
to the criteria under the appropriate date field/column.

These queries will not be updateable but it sounds like they might be action queries.

Duane
Hook'D on Access
MS Access MVP

RE: Using Between And date selector in Access query

(OP)
Hi Duane, thanks for the reply. All the queries are Select queries simply pulling data from a variety of tables. I did try a one record table with from & to dates but made the mistake of joining it to another table; I'll try this approach again but without the join and see what happens, thank you.
Clive

RE: Using Between And date selector in Access query

You could add a text box to a form named StartDate, the second text box named EndDate. Populate these with the desired dates, then set your query criteria to look for the loaded dates. Between [Form]![Startdate] and [Form]![EndDate].

Thanks for the help.
Greg

RE: Using Between And date selector in Access query

If you follow razchip's tip, you may find this helpful.

combo

RE: Using Between And date selector in Access query

(OP)
Thank you razchip & combo, will see what this allows me to do.
Clive

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close