Export to Excel using a dynamic query 1

[jwoodland]

I am currently exporting into Excel using the 'OutputTo' method and a stored query. I have a form with two combos on to filter the rows exported. This works fine. However I want to take it to the next stage and add some check boxes to the form which would (when checked) remove some of the fields in the query so the user only exports the columns he/she requires.

I think this needs to go into a dynamically built DAO SQL string but am unsure where to start.

Any pointers would be gretaly appreciated.

 

[AlexCuse]

To get you started:

if checkbox1 = Yes then 
   field1 = "[field 1 name], "
else field1 = ""

'etc....

if checkboxn = Yes then
   fieldn = "[field n name] "
else fieldn = ""

strSQL = "select " & field1 & field2 & fieldn & "from table where something = " & someParameter

Note that you wil need to make sure fieldn is one that is always present, or you will have a stray comma in your string (or you could handle this problem in your code).

Hope this helps to get you started.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[CautionMP]

jwoodland,
When you add the check boxes to the form will they be bound to a field in the underlying table? If so you could continue to use the same method you already have and just add an additional filter for the Yes/No fields.

Another method that would allow you to continue to use the [tt]OutputTo[/tt], assuming that your form has a key field you can use to uniquely identify the records the user wants, would be to build a filter in the form of an '[tt]IN[/tt]' clause that could be passed back to a standard query.

Last method would be to use OLE automation as a replacement for [tt]OutputTo[/tt] and create your own spreadsheet using the form recordset.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jwoodland]

Thanks for these replies.

CauitionMP: The form is not bound at all and is simply a 'criteria' select form. The stored query run is a query linking 3 tables.

I have successfully used AlexCuse pointer to buils the SQL string required but am now havinbg diificulty passing this string into the 'OutPutTo' method. I essentiallty need to get the results into an Excel workbook.

 

[AlexCuse]

Can you populate a recordset using strSQL?

A wise man once said
"The only thing normal about database guys is their tables".

 

[CautionMP]

jwoodland,
Sounds like I missed the target on the original post. A couple more thoughts that build on what AlexCuse said. If you can build the SQL string dynamically with all the correct filters you could create a new [tt]QueryDef[/tt] and call it with [tt]OutputTo[/tt] or [tt]TransferSpreadsheet[/tt]. If you don't want to create a [tt]QueryDef[/tt] for what sounds like an Ad-Hoc request you might look at something like this:
[tab]thread705-1260935 : Export A Dynamic Query TO Excel

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)