Report export to excel by filter and columns

[Tfa]

So here is what i want to do
I have a report created by two tables no problem so far
i what the user to be able to select the columns that he wants to export and also filter them
what i have done so far is that i created check buttons and each check button contains on part of the sql statement then i connect all of then and i have the ecxel that i want
for example

If Me.Check1 = True Then
sql1 = "Titles.ItemID as ItemID,"
Else: sql1 = ""
End If


...

sqlsum = "select " + sql1
sqlrest = " FROM Titl.Iteme = P_Item.ang_Id;"
sqltotal = sqlsum + sqlrest

strQry = "TempQueryName"

Set db = CurrentDb
Set Qdf = db.CreateQueryDef(strQry, sqltotal)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
strQry, "C:\ads\asd.xls", True

DoCmd.DeleteObject acQuery, strQry


but that will not give me the fitering that i want

any suggestions ?

 

[Gasman]

" WHERE Titl.Iteme = P_Item.ang_Id;" ?

 

[Tfa]

lets make an update
what i have done so far
after i choose the columns i make an sql query and i save it with the name TempQueryName then i get the filter as a string filter2 = me.filter
then i replace the part of the filter with tha name of tha table and from
([B0017_Titles_TitlesRest_Report_Query].[ItemID]<=58255) i make it
([TempQueryName].[ItemID] <= 58255)
now can i add this final line to my already existing query so that i can filter it our and then get my excel file ???
that is the question

 

[gemma-the-husky]

Personally, I would let him export the whole lot, and then let him delete the columns he doesn't want.

Sometimes it's difficult adding all the flexibility to the database, and it's easier to do some things outside the database.

 

[Gasman]

lets make an update
what i have done so far
after i choose the columns i make an sql query and i save it with the name TempQueryName then i get the filter as a string filter2 = me.filter
then i replace the part of the filter with tha name of tha table and from
([B0017_Titles_TitlesRest_Report_Query].[ItemID]<=58255) i make it
([TempQueryName].[ItemID] <= 58255)
now can i add this final line to my already existing query so that i can filter it our and then get my excel file ???
that is the question

I would say Yes, just add it to the WHERE clause.?
However I would probably do it in the query as part of TempQueryName query

 

[Tfa]

well Gasman i did some ting like that

sqlrest = Left(sqlrest, Len(sqlrest) - 1)

sqltotal = sqlsum + sqlrest + " where " + filter2
MsgBox sqltotal
strQry = "TempQueryName"
If test <> "" Then
Set db = CurrentDb
Set Qdf = db.CreateQueryDef(strQry, sqltotal)


'DoCmd.Close acQuery, "TempQueryName"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQry, "C:\access\" + test + ".xls", True
DoCmd.Close acQuery, "TempQueryName"
DoCmd.DeleteObject acQuery, strQry

and it works like a charm but first i had to cut "[([B0017_Titles_TitlesRest_Report_Query]."
i also tried the allowfilter but i couldnt find a way to save the results
not that it matters any more so i think that this is over time for the next job

Gemma the husky my Boss didn't like your idea
he said that's the way that an academic thinks and not a professional SD that works in a company