TransferSpreadsheet and parameter query help?

  • Thread starter Thread starter chemicals
  • Start date Start date
C

chemicals

I need to run a query with a parameter and then output the results to a
spreadsheet.
I can use TransferSpreadsheet to read from a table - this works fine. I
need to do it with a parameter query to a SQL Server. I am using ADO for all
my database access..

Does TransferSpreadsheet alloe the passing of a query AND a parameter? I
can't seem to make it work.

Thanks
 
here is the code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"get_materials, modInit.strOutputPath & modInit.ContractCode &
"_MaterialsList.xls", True

"get materials" is a query requiring a parameter...

I also want this to be done in the background (i.e. no UI control from form)
 
If this is a crosstab query, you must declare the parameter in the query.
It's a good idea to do that anyway. Choose Parameters on the query menu,
enter the full name of the parameter, and indicate the data type.

How can I return based upon strings? For example if a param is "al", return
alaska and albany.

WHERE Status Like "*" & [Param1] & "*" AND Status Like "*" & [Param2] & "*"

*aaa* will bring all the words that conatain aaa
aaa* will bring all the words that start with aaa
*aaa will bring all the words that end with aaa

Allen Browne has a nice example here:
http://allenbrowne.com/ser-67.html

I really like Martin Green's stuff too:
http://www.fontstuff.com/access/acctut01.htm

Regards,
Ryan---
 
Back
Top