Exporting query to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ame - 16 Aug 2005 16:13 GMT

I am trying to export a query to Excel using TransferSpreadsheet. I am
running the code from a button on a form. The code works beautifully, but I
need to add a search criteria. I want the user to be able to choose a year
from a combo box on the form and then have the code choose those records and
only generate these entries in the Excel spreadsheet. How would I fit such a
parameter into the Transfer Spreadsheet method?

Signature

Amy

Reply to this Message

Klatuu - 16 Aug 2005 16:54 GMT

The TransferSpreadsheet has no search criteria capabilities. You will have to
put the search criteria in the query. You will establish the parameters for
the query the same as you would for any other query.

> I am trying to export a query to Excel using TransferSpreadsheet. I am
> running the code from a button on a form. The code works beautifully, but I
> need to add a search criteria. I want the user to be able to choose a year
> from a combo box on the form and then have the code choose those records and
> only generate these entries in the Excel spreadsheet. How would I fit such a
> parameter into the Transfer Spreadsheet method?

Reply to this Message

'69 Camaro - 16 Aug 2005 17:11 GMT

Hi, Amy.

One cannot supply query criteria to the TransferSpreadsheet method to select
only certain records. This method only transfers the final query to Excel.
One must alter the query to accommodate the criteria in the form. Here is an
example of adding this criteria to the WHERE clause in the query:

WHERE (DatePart("yyyy", SomeDate) = NZ(Forms!FormName!cboYear,
DatePart("yyyy", Date())))

... where SomeDate is the date field, and FormName is the name of the form.
In case no year is selected, then the current year will be supplied to the
query.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are Ripley@CASInternet.Net and scott@ripleysoftware.com

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Reply to this Message

ame - 16 Aug 2005 20:59 GMT

Thank you! I'll give your suggestion a try and see how it works.

Signature

Amy

> Hi, Amy.
>
[quoted text clipped - 36 lines]
> > only generate these entries in the Excel spreadsheet. How would I fit such a
> > parameter into the Transfer Spreadsheet method?

Reply to this Message