Edit query with Sql using user input

[jojo1uf]

Ok, I am baffled. I have tried numerous things.................

I want to let the user select the criteria to perform a search. Then I would
like to export the search results to excel.

(Sample code if user selects an ENTITY. If they select more than one
criteria, I concatenate the strSQL to include all criteria.)
strSQL = "SELECT provider FROM invoicing WHERE provider = " & "'" &
Forms!SEARCH!ListEntity & "';"

I have already created a query with the name QSummary. So my intention was
to edit the QSummary somehow with strSQL.This is where my problem is. How do
I edit the results of an existing query with code in order to export to Excel?

DoCmd.TransferSpreadsheet acExport, 8, "QSummary", "C:\INVOICING.xls"
FollowHyperlink "C:\INVOICING.xls"


Any and all help is greatly appreciated. Thank you.

 

[Steve Schapel]

Jojo,

Why don't you put [Forms]![SEARCH]![ListEntity] directly into the
criteria of the Provider field in the QSummary query?

 

[jojo1uf]

Hi Steve,
Thank you for your quick response.

However, I think that I want something a bit different.

I created application with a bunch of forms. The users only see the forms.
They do not see the queries in design mode. So, when the user clicks on the
command button 'search', a form opens and the user can input into textboxes
and/or select from a few listboxes in order to create their search criteria.
This search criteria is stored in a strSQL. I would like to export the
search results into Excel. What I want to do is to send the strSQL to an
already existing query. And export that query to Excel. Do you think that
this is possible? Thanks.

Jojo,

Why don't you put [Forms]![SEARCH]![ListEntity] directly into the
criteria of the Provider field in the QSummary query?

--
Steve Schapel, Microsoft Access MVP

Ok, I am baffled. I have tried numerous things.................

I want to let the user select the criteria to perform a search. Then I would
like to export the search results to excel.

(Sample code if user selects an ENTITY. If they select more than one
criteria, I concatenate the strSQL to include all criteria.)
strSQL = "SELECT provider FROM invoicing WHERE provider = " & "'" &
Forms!SEARCH!ListEntity & "';"

I have already created a query with the name QSummary. So my intention was
to edit the QSummary somehow with strSQL.This is where my problem is. How do
I edit the results of an existing query with code in order to export to Excel?

DoCmd.TransferSpreadsheet acExport, 8, "QSummary", "C:\INVOICING.xls"
FollowHyperlink "C:\INVOICING.xls"


Any and all help is greatly appreciated. Thank you.

 

[Steve Schapel]

Jojo,

Yes, this is possible, but as I hinted at before, more complicated than
necessary. To me, it would be easier to just put the criteria straight
into the query, and I still can't see why you don't do that.

Otherwise, something like this (caution: air code, untested!)

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourOriginalQuery"­)
BaseSQL = qdf.SQL
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE blabla ... your
criteria"
qdf.SQL = strSQL
DoCmd.TransferSpreadsheet ....
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

 

[jojo1uf]

Steve, Thank you very much for your help and expertise. With your help, I
finally got on the right track and figured it out!!!!!!!!!!!!