Excel query of Access query with varying date 1

[rgreen511]

I am trying to modify a report that is built in Excel. Currently, an Access DB is opened, and data is exported using several queries and macros to 4 Excel workbooks, then the Excel report is opened, and it pulls the data from the 4 workbooks, very clunky. I'm trying to pull the data directly from the database. The problem is that the data needs filtered to only include a days worth of data, and this date changes.

I can use Microsoft Query to try to query the existing Access queries (similar to this

http://www.tek-tips.com/viewthread.cfm?qid=1107089),

however, the queries are parameter queries requiring a date. Since the ODBC driver for Access queries does not pass parameters between the two programs, I am receiving a "Too Few Parameters. Expected 1" error. I looked in Microsoft Knowledge Base KB207143 and KB209203, which addressed the problem, but gave no workable solution.

An alternate solution would be to just duplicate the Access query in Microsoft Query, however there are several IIf statements in the Access Query, and these don't exist in Microsoft Query. Also, I can't find an easy way to change the dates for the query each time the report is run, it seems to lock the dates in. If there were a way to change the filter dates each time the query were run, I could do it this way.

So, the two questions are: 1)how to query a query and pass a date to the Access query, or 2)how to do IIf equations and vary the filter string (date) for the Microsoft Query in Excel.

Thanks, and sorry for the long post, but I've spent too long trying to figure this out already.

 

[ETID]

You can set Criteria to look at a cell value in Excel...

Just remove the parameters from the access query and then add them in the MsQuery editor.

Once the data is returned to the worksheet, you can right click and choose parameters from the menu.

Here is where you can tell MsQuery to monitor a cell(s) for querying as well as an option to requery when the cell value changes.

 

[rgreen511]

I think this will do exactly what I want, thank you very much!

One more question, I have it popping a prompt to ask me for the date. Is there a way to query using that date and the day before? I'm not sure what syntax to use. I tried subtracting 1, but I don't know how to indicate the date that was just entered. Thanks.

 

[ETID]

An example criteria for the date in the MsQuery editor...

>=[date_1]-1 And <=[date_1]

then redirect to worksheet referencing the date cell

or

>=[date_1] And <=[date_2]

Then point the parameters to a cells in the workbook that contains #1 a date and #2 a cell with a formula that subtracts 1 from a date.

 

[rgreen511]

OK, thanks. I think the key there is pointing the parameter to a cell, which I will try to do. When I have the query popping to get the dates, it would pop twice for each of those examples, but shouldn't if I reference it.

Thanks again, this was so simple, I'm not sure why I never noticed MS Query could do the parameters instead of Access.

 

[rgreen511]

I have two question about new issues that just arose with this report.

1) Is there a function similar to DateAdd in MS Query, because several of the Access Queries use DateAdd in their criteria, and I can't find a simple way to implement that in the MS Query?

2) One of the Access queries I am MS Querying does not have parameters, however it references another query that does. Will I be able to eliminate these parameters and use the MS Query, or will I have to MS Query both of the Access Queries separately?

 

[ETID]

You may have to resolve "DateAdd()" with the Datediff() Excel Function and point MsQuery parameter to that cell.

As far as your nested query with paramters...if you remove them it should still work via MsQuery, but might take a wee bit longer to run.