Using Between And date selector in Access query

[Skyshadow5]

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

 

[Andrzejek]

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.

 

[Skyshadow5]

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

 

[Andrzejek]

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.

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.

 

[Skyshadow5]

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

 

[dhookom]

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
[pre] Between FromDate and ToDate[/pre]
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

 

[Skyshadow5]

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

 

[razchip]

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

 

[combo]

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

combo

 

[Skyshadow5]

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