Pass Date criteria to upto 15 queries from one input

[JohnLee]

Good afternoon,

Is it possible to pass a date range criteria to upto 15 queries from one input in one instance.

What I would like to do is using a form as the front end is enter a start date and an end date that would be passed to mulitiply queries that will then run consecutively using that date range.

Any assistance or pointers would be most appreciated.

Regard

John

 

[Trevor G]

Set the criteria for each query to use a search form date parameter and then use a MACRO to run all the queries in one hit, you can always switch the warnings off in the Marco so you aren't seeing the warnings like you are about to run a select query or append query etc, in the Macro you can also add a message to say its done then switch the warnings back on. Then drag the macro onto the search form and it will create a button.

 

[JohnLee]

Hi Trevor,

Thanks for your reply, I'm not familiar with the Search Form Date Parameter in a query, I note your using a later version of Access than I am, I'm useing Access 2000, so is that a new feature in later version.

Regards

John

 

[Trevor G]

No John using date parameters aren't new. Try this out for One Query and then you can apply it to the rest.

Add a Form and then add 2 textbox name them as txtStartDate and txtEndDate format them to use Date Format and then save the form (don't close it) something like frmSearch.

Next open one of the queries in design view and find the date field you want to use, then below the date field in the Criteria box click and open the Build Wizard looks like a wand with stars by the side of it. In the Build Wizard type in Between then search on the left in the Forms Section and double click it to expand then all open forms, then the search form and then double click the txtStartDate and then type in And and then double click the txtEndDate, that then gives you the criteria. Click Ok and save the query and close it.

Next create a new Macro and in the arguments select to Open a Query and in the bottom of the Arguments find the query name, at this point save the Macro and name it something like mcrSearch, close the Macro.

Next the search form is still open in design view so then press F11 to show the database window and drag the macro into the form, save the form and then change the view add some dates into the textbox and click the button and it should run and open the query, once you have it working then you need to add the same steps to each of the other queries to use the same form and criteria and then add each argument to the macro so if you have 15 queries in the macro you end up with 15 arguments plus stop warnings etc.

I hope this helps and you can work your way through it.

 

[JohnLee]

Hi Trevor,

Thanks very much for your response and excellent explanation. In all the years of been using Access I never knew about the ability to use the wizard in the criteria of a query.

Goes to show there's is no much still to learn.

Thanks once again and I'll let you know how I get on

Cheers

John

 

[JohnLee]

Hi Trevor,

That works a treat, I'm now moving onto my next bit of my process, so I may ask some more questions depending on whether or not I run into difficulties.

Cheers

John

 

[Trevor G]

Happy to help when I can John and thanks for letting me know it is working so far.

 

[JohnLee]

Good day Trevor,

Having followed your instructions to open the 15 queries with the same date range criteria, I’ve been trying to work out how to then export the contents of each of those queries into the same excel spreadsheet, each occupying their own tab.

The problem I am coming across is that when I try to use either the Transferspreadsheet or the OutputTo option I get the following information displayed:

eFlow Management Information System can’t save the output data to the file you’ve selected.

*The file may be open. If so, close it, and then save the output data to the file again.
*If you are using a template, check to make sure the template exists.
*If the file isn’t open, check to make sure that you have enough free disk space.


Action Failed: Macro Name: mcr_ExportAdHoxStdDGToExcel

Condition: True

Action Name: OutputTo

Arguments: 1, stdDGQuesResponseAdhocStats, MicrosoftExcel(*.xls), M:\Customer Satisfaction\ScanMthVols, No,

So from this I understand that whilst the queries are open access can’t export the data to excel, yet I can export each query in turn manually to excel, but I’m not able to put them into the same workbook, they have to be in their own individual workbook.

Is there a way I can pass my desired date range to each of the queries and then export them to a single excel workbook with each occupying their own tabs.

Any assistance would be most appreciated.

Regards

John

 

[Trevor G]

All you should need to do John is point the queries to the same workbook and it will create the additional tabs for you and name each as per the query name, look at the following, all I am doing is listing 1 query and 1 table, you can change this to the 15 queries, to be exported to the same workbook and when I open it the workbook has the 2 worksheets

Function Export()
DoCmd.TransferSpreadsheet acExport, 10, "qrySendReport", "L:\Sendreport.xlsx", True
DoCmd.TransferSpreadsheet acExport, 10, "TBL_Monhly Usage", "L:\Sendreport.xlsx", True
End Function

 

[JohnLee]

Hi Trevor,

Thanks for your response, I have set up other queries to output to an excel spreadsheet in the same mannor as your last post, they work fine, but it doesn't work with the method in your post of 14 Dec 12, 06:56.

I have tried it and the error message I posted gets generated. I think that because I am passing the date range criteria to the queries dynamically and because the queries are then opened and not closed access generates that message.

If I close the queries after passing the date range criteria, they lose the date range criteria and therefore access doesn't export them to the spreadsheet.

So in each query I have the following in the criteria row beneath the Date field : Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate]

In the form frmSearch I have text boxes txtStartDate and txtEndDate, I also have a button that points to the macro mcr_ExportAdHocStdDGToExcel

In the macro I have the following for each query

Action : Openquery

Query Name : stdDGQuesResponseAdhocStats
View: Datasheet
Data Mode : Edit

I follow this process for each query that I want to pass the date range to.

when I enter the date range in the frmSearch and click on the button that runs the mcr_ExportAdHoxStdDgToExcel macro all the queries are then consecutively opened for that specified date range, which is good.

So I thought that by adding the following to the Macro I could then export the queries to excel each occupying their own tabs:

Action: TransferSpreadsheet.

Transfer Type : Export
Spreadsheet Type : Microsoft Excel 8-9
Table Name : stdDGQuesResponseAdhocStats
File Name : M:\Customer Satisfaction\ScanMthVols\Adhox.xls
Has Field Names : Yes

I created a line for each query and set it up exactly the same as above placing these all at the end of the last open query action, and then run the macro again from the frmSearch and I a similar message to that in my earlier post, I then placed this Action after each Openquery Action and the same problem came up.

I then tried using the OutputTo method in the macro and did exactly the same as above and again this message came up.

So I'm guessing that what I'm trying to achieve can't be done with a macro.

It would seem that that if the date range is set as a constant i.e. Between Date() And Date()-5 this will work, but if you try this method Between [Forms]![frmSearch]![txtStartDate] And [Forms]![frmSearch]![txtEndDate], Access doesn't like it.

I'll give it all another go, but if you have any other suggestions as to how I might achieve this, it would be most appreciated.

Regards

John

 

[JohnLee]

Hi Trevor,

I've found what I was doing wrong, and it's now working. I had missed off the name of the file to export to and that was why the message was being generated.

I also noted that the queries remained open and so I needed to include the close action in the macro for each query.

And so now onto the next bit, which is to format the spreadsheet. I may seek further advice from you on that too.

Thanks once again

John

 

[Trevor G]

Well done John.

My advice about the formatting, is to look to record a macro and then look to format a sheet and then stop recording this will then give you the code you need to then make it work for the other sheets, give it a go and upload the code with a few instructions and I will take a look to make it work for the workbook for you, and then combine it so that you can automate opening the workbook once the transferspreadsheets has happened.

 

[JohnLee]

Thanks Trevor,

As today is my last day at work until the New Year, it will most likely be in the new year before I can complete the work on that part [which I am looking forward to], as I have to do a handover most of today to cover whilst I'm off over the Christmas week.

Thanks for all you help so far and I hope you have a good Christmas and New Year and I'll post how I get on with the formatting part in the New Year.

Regards

John