Kevin Smith
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
So how do I open a query and apply my strWhere criteria?
Kevin
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this an ADP again?
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.Colum n(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.
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.Colum
If strWhere <> "" Then strWhere = strWhere & " AND "
strWhere = strWhere & "[StateMailing]='" & Me.cboSelectState.Column(0
End If
If Nz(Me.cboCarrier.Column(0)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
a view
And you want that View output to an Excel file - but with a criteria applied to it?
ASKER
Exactly :)
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
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
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.
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.
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
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
Can you post the SQL that your report is based on?
Good luck!
~Jillyn