asked on

Open a query in xls with where criteria

I have a report that opens with where selections from a form (strWhere). What I would like to do is open the query in Excel format without having to build a report (they really won't use the report anyway, they'll just want it in Excel).

So how do I open a query and apply my strWhere criteria?

Kevin

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jillyn_D

Hi ksmithscs,

Can you post the SQL that your report is based on?

Good luck!
~Jillyn

ASKER CERTIFIED SOLUTION

Jillyn_D

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

Is this an ADP again?

Kevin Smith

ASKER

peter...it's an access adp with SQL backend, so the criteria in the query probably won't work.

Here's my SQL...

Dim stDocName As String

Dim strWhere As String

If Nz(Me.cboApp, "") <> "" Then strWhere = "[ApplicantStatus]='" & Me.cboApp & "'"

If Nz(Me.cboAppSub, "") <> "" Then
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "[ApplicantStatusSub]='" & Me.cboAppSub & "'"
End If

If Nz(Me.cboSelectState.Column(0), "") <> "" Then
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "[StateMailing]='" & Me.cboSelectState.Column(0) & "'"
End If

If Nz(Me.cboCarrier.Column(0), "") <> "" Then
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "[OriginalCarrier]='" & Me.cboCarrier.Column(0) & "'"
End If

stDocName = "reportname"
DoCmd.OpenReport stDocName, acPreview, , strWhere

The report is based on a query.

SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Smith

ASKER

a view

Leigh Purvis

And you want that View output to an Excel file - but with a criteria applied to it?

Kevin Smith

ASKER

Exactly :)

peter57r

You cannot pass parameters to a View.

If you want the equivalent of parameterised views you can use a sql function. I assume this is possible in an adp but don't know for sure.

Look here for a simple example:
http://www.informit.com/articles/article.asp?p=130855&seqNum=3&rl=1
About half way down .......
Using Inline Functions in Views

Pete

Leigh Purvis

Whoops - I forgot about this question (even though it was just yesterday). :-S

ADP's do offer similar exporting of objects much as MDBs do with queries.
However as with queries, you don't want parameter quests popping up through the UI (or at least you *shouldn't* want them :-p).
Consequently - having a form/report isn't a bad thing to export. The recordsource of forms and reports can be manipulated internally. Or you could open a recordset and assign that to an open spreadsheet - you have huge control then.

Kevin Smith

ASKER

Hey guys,

I split points to all who offered input, but I evenutally just built the report and added the toolbar "analyze with excel" function...that worked just as well with what they need.

Thanks!
Kevin