Updating a query via Excel macro

[Coop]

I have a macro which exports data from Excel to Access. While obtaining a value in Excel, can the criteria field on a query be changed to this value? Thank you.

 

[SydneyGeek]

Is this query an MSQuery originating in Excel, or is it an Access query that you use to filter the data?

If it's Access, you can use variables if you turn the query into a SQL statement but we need to see what you have before giving any detailed help.

Go to the SQL view of the query, copy the code, and post it to the board

If it's Excel, consider using ADO code to push the data to Access. That way you can use cell values as your parameters to pull data from Access, or to filter data prior to exporting it.


Denis

 

[Coop]

To answer your first question, it is an Access query.
Here is the SQL view of the query.

SELECT [Bank Data].BusinessDay, [Bank Data].Date, [Bank Data].IncDecBuyNeed
FROM [Bank Data]
WHERE ((([Bank Data].BusinessDay)=6))
ORDER BY [Bank Data].Date;


This is what I would like to do. I want to take the value in cell A1 on Sheet1 and change the parameter for the business day (which is at 6) to this value.

 

[SydneyGeek]

I haven't done a lot with MSQuery from Excel, but here's a link to a solution: http://www.exceltip.com/newsgroup/topic-24153|Using_Excel_cell_contents_for_MS_Query_parameter?
This could also be useful: http://www.rdg.ac.uk/ITS/info/training/notes/excel/query/

If you're going to set your parameters from Excel, I would recommend targeting the table directly -- not the criteria-based query.

Denis