Export Search results from a search form to Excel

Hi all, I have a problem. I have designed a search form in Microsoft
Access 2003, which contains a subform that displays the results. The
search form contains 7 text boxes to enter search criteria and the
user can enter one or more. I am now trying to figure out a way to
export the results that appear in the sub form to an excel spreadsheet
using a command button on my search form. My search form is based on a
BuildFilter Function:

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null

If Me.txtCC > "" Then
varWhere = varWhere & "[Cost Centre] LIKE ""*" & Me.txtCC & "*"" AND "

End If

If Me.txtDescription > "" Then
varWhere = varWhere & "[Contract Description] LIKE ""*" &
Me.txtDescription & "*"" AND "

End If

If Me.txtHNC > "" Then
varWhere = varWhere & "[HNC] LIKE ""*" & Me.txtHNC & """ AND "

End If

If Me.txtPE > "" Then
varWhere = varWhere & "[Paying entity] LIKE ""*" & Me.txtPE & "*"" AND
"

End If

If Me.txtTC > "" Then
varWhere = varWhere & "[Tier Code] LIKE ""*" & Me.txtTC & "*"" AND "

End If

If Me.txtVendor > "" Then
varWhere = varWhere & "[Vendor] LIKE ""*" & Me.txtVendor & "*"" AND "

End If

If Me.txtCat > "" Then
varWhere = varWhere & "[Category] LIKE ""*" & Me.txtCat & "*"" AND "

End If

If IsNull(varWhere) Then
varWhere = ""
Else

'varWhere = "WHERE" & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

And behind the cmdSearch_Click()
Private Sub cmdSearch_Click()

Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
BuildFilter

Me.subfrmSearch.Requery

End Sub

PLEASE HELP

Re: Export Search results from a search form to Excel

On Nov 13, 5:20 am, chuch wrote:
> Hi all, I have a problem. I have designed a search form in Microsoft
> Access 2003, which contains a subform that displays the results. The
> search form contains 7 text boxes to enter search criteria and the
> user can enter one or more. I am now trying to figure out a way to
> export the results that appear in the sub form to an excel spreadsheet
> using a command button on my search form. My search form is based on a
> BuildFilter Function:
>
> Private Function BuildFilter() As Variant
> Dim varWhere As Variant
>
> varWhere = Null
>
> If Me.txtCC > "" Then
> varWhere = varWhere & "[Cost Centre] LIKE ""*" & Me.txtCC & "*"" AND "
>
> End If
>
> If Me.txtDescription > "" Then
> varWhere = varWhere & "[Contract Description] LIKE ""*" &
> Me.txtDescription & "*"" AND "
>
> End If
>
> If Me.txtHNC > "" Then
> varWhere = varWhere & "[HNC] LIKE ""*" & Me.txtHNC & """ AND "
>
> End If
>
> If Me.txtPE > "" Then
> varWhere = varWhere & "[Paying entity] LIKE ""*" & Me.txtPE & "*"" AND
> "
>
> End If
>
> If Me.txtTC > "" Then
> varWhere = varWhere & "[Tier Code] LIKE ""*" & Me.txtTC & "*"" AND "
>
> End If
>
> If Me.txtVendor > "" Then
> varWhere = varWhere & "[Vendor] LIKE ""*" & Me.txtVendor & "*"" AND "
>
> End If
>
> If Me.txtCat > "" Then
> varWhere = varWhere & "[Category] LIKE ""*" & Me.txtCat & "*"" AND "
>
> End If
>
> If IsNull(varWhere) Then
> varWhere = ""
> Else
>
> 'varWhere = "WHERE" & varWhere
>
> If Right(varWhere, 5) = " AND " Then
> varWhere = Left(varWhere, Len(varWhere) - 5)
> End If
> End If
>
> BuildFilter = varWhere
>
> End Function
>
> And behind the cmdSearch_Click()
> Private Sub cmdSearch_Click()
>
> Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> BuildFilter
>
> Me.subfrmSearch.Requery
>
> End Sub
>
> PLEASE HELP

Use the TransferSpreadsheet method.

Re: Export Search results from a search form to Excel

On Nov 13, 3:20 am, chuch wrote:
> Hi all, I have a problem. I have designed a search form in Microsoft
> Access 2003, which contains a subform that displays the results. The
> search form contains 7 text boxes to enter search criteria and the
> user can enter one or more. I am now trying to figure out a way to
> export the results that appear in the sub form to an excel spreadsheet
> using a command button on my search form. My search form is based on a
> BuildFilter Function:
>
> Private Function BuildFilter() As Variant
> Dim varWhere As Variant
>
> varWhere = Null
>
> If Me.txtCC > "" Then
> varWhere = varWhere & "[Cost Centre] LIKE ""*" & Me.txtCC & "*"" AND "
>
> End If
>
> If Me.txtDescription > "" Then
> varWhere = varWhere & "[Contract Description] LIKE ""*" &
> Me.txtDescription & "*"" AND "
>
> End If
>
> If Me.txtHNC > "" Then
> varWhere = varWhere & "[HNC] LIKE ""*" & Me.txtHNC & """ AND "
>
> End If
>
> If Me.txtPE > "" Then
> varWhere = varWhere & "[Paying entity] LIKE ""*" & Me.txtPE & "*"" AND
> "
>
> End If
>
> If Me.txtTC > "" Then
> varWhere = varWhere & "[Tier Code] LIKE ""*" & Me.txtTC & "*"" AND "
>
> End If
>
> If Me.txtVendor > "" Then
> varWhere = varWhere & "[Vendor] LIKE ""*" & Me.txtVendor & "*"" AND "
>
> End If
>
> If Me.txtCat > "" Then
> varWhere = varWhere & "[Category] LIKE ""*" & Me.txtCat & "*"" AND "
>
> End If
>
> If IsNull(varWhere) Then
> varWhere = ""
> Else
>
> 'varWhere = "WHERE" & varWhere
>
> If Right(varWhere, 5) = " AND " Then
> varWhere = Left(varWhere, Len(varWhere) - 5)
> End If
> End If
>
> BuildFilter = varWhere
>
> End Function
>
> And behind the cmdSearch_Click()
> Private Sub cmdSearch_Click()
>
> Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> BuildFilter
>
> Me.subfrmSearch.Requery
>
> End Sub
>
> PLEASE HELP

To add to what OldPro said. In your statement,

Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
BuildFilter

modify it as such and create a temporary table that you can export to
Excel.

docmd.runsql "select * into tblTemp from qrySearch where " &
buildfilter

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"tblTemp", strFile

where strFile is the path of the file you want the data to go into.
For example, you can have

strFile = "c\Temp\temp.xls"

Good luck!

Dorothy
financialreporting@googlegroups.com

Re: Export Search results from a search form to Excel

Hi, i dont have a problem displaying my results from the query
"qrySearch". It is just that the query is not being filtered before
the results are displayed in excel, so the whole query is being
exported whereas all i want is the results of the search



Dorothy wrote:
> On Nov 13, 3:20 am, chuch wrote:
> > Hi all, I have a problem. I have designed a search form in Microsoft
> > Access 2003, which contains a subform that displays the results. The
> > search form contains 7 text boxes to enter search criteria and the
> > user can enter one or more. I am now trying to figure out a way to
> > export the results that appear in the sub form to an excel spreadsheet
> > using a command button on my search form. My search form is based on a
> > BuildFilter Function:
> >
> > Private Function BuildFilter() As Variant
> > Dim varWhere As Variant
> >
> > varWhere = Null
> >
> > If Me.txtCC > "" Then
> > varWhere = varWhere & "[Cost Centre] LIKE ""*" & Me.txtCC & "*"" AND "
> >
> > End If
> >
> > If Me.txtDescription > "" Then
> > varWhere = varWhere & "[Contract Description] LIKE ""*" &
> > Me.txtDescription & "*"" AND "
> >
> > End If
> >
> > If Me.txtHNC > "" Then
> > varWhere = varWhere & "[HNC] LIKE ""*" & Me.txtHNC & """ AND "
> >
> > End If
> >
> > If Me.txtPE > "" Then
> > varWhere = varWhere & "[Paying entity] LIKE ""*" & Me.txtPE & "*"" AND
> > "
> >
> > End If
> >
> > If Me.txtTC > "" Then
> > varWhere = varWhere & "[Tier Code] LIKE ""*" & Me.txtTC & "*"" AND "
> >
> > End If
> >
> > If Me.txtVendor > "" Then
> > varWhere = varWhere & "[Vendor] LIKE ""*" & Me.txtVendor & "*"" AND "
> >
> > End If
> >
> > If Me.txtCat > "" Then
> > varWhere = varWhere & "[Category] LIKE ""*" & Me.txtCat & "*"" AND "
> >
> > End If
> >
> > If IsNull(varWhere) Then
> > varWhere = ""
> > Else
> >
> > 'varWhere = "WHERE" & varWhere
> >
> > If Right(varWhere, 5) = " AND " Then
> > varWhere = Left(varWhere, Len(varWhere) - 5)
> > End If
> > End If
> >
> > BuildFilter = varWhere
> >
> > End Function
> >
> > And behind the cmdSearch_Click()
> > Private Sub cmdSearch_Click()
> >
> > Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> > BuildFilter
> >
> > Me.subfrmSearch.Requery
> >
> > End Sub
> >
> > PLEASE HELP
>
> To add to what OldPro said. In your statement,
>
> Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> BuildFilter
>
> modify it as such and create a temporary table that you can export to
> Excel.
>
> docmd.runsql "select * into tblTemp from qrySearch where " &
> buildfilter
>
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
> "tblTemp", strFile
>
> where strFile is the path of the file you want the data to go into.
> For example, you can have
>
> strFile = "c\Temp\temp.xls"
>
> Good luck!
>
> Dorothy
> financialreporting@googlegroups.com

Re: Export Search results from a search form to Excel

On Nov 14, 3:44 am, chuch wrote:
> Hi, i dont have a problem displaying my results from the query
> "qrySearch". It is just that the query is not being filtered before
> the results are displayed in excel, so the whole query is being
> exported whereas all i want is the results of the search
>
>
>
> Dorothy wrote:
> > On Nov 13, 3:20 am, chuch wrote:
> > > Hi all, I have a problem. I have designed a search form in Microsoft
> > > Access 2003, which contains a subform that displays the results. The
> > > search form contains 7 text boxes to enter search criteria and the
> > > user can enter one or more. I am now trying to figure out a way to
> > > export the results that appear in the sub form to an excel spreadsheet
> > > using a command button on my search form. My search form is based on a
> > > BuildFilter Function:
>
> > > Private Function BuildFilter() As Variant
> > > Dim varWhere As Variant
>
> > > varWhere = Null
>
> > > If Me.txtCC > "" Then
> > > varWhere = varWhere & "[Cost Centre] LIKE ""*" & Me.txtCC & "*"" AND "
>
> > > End If
>
> > > If Me.txtDescription > "" Then
> > > varWhere = varWhere & "[Contract Description] LIKE ""*" &
> > > Me.txtDescription & "*"" AND "
>
> > > End If
>
> > > If Me.txtHNC > "" Then
> > > varWhere = varWhere & "[HNC] LIKE ""*" & Me.txtHNC & """ AND "
>
> > > End If
>
> > > If Me.txtPE > "" Then
> > > varWhere = varWhere & "[Paying entity] LIKE ""*" & Me.txtPE & "*"" AND
> > > "
>
> > > End If
>
> > > If Me.txtTC > "" Then
> > > varWhere = varWhere & "[Tier Code] LIKE ""*" & Me.txtTC & "*"" AND "
>
> > > End If
>
> > > If Me.txtVendor > "" Then
> > > varWhere = varWhere & "[Vendor] LIKE ""*" & Me.txtVendor & "*"" AND "
>
> > > End If
>
> > > If Me.txtCat > "" Then
> > > varWhere = varWhere & "[Category] LIKE ""*" & Me.txtCat & "*"" AND "
>
> > > End If
>
> > > If IsNull(varWhere) Then
> > > varWhere = ""
> > > Else
>
> > > 'varWhere = "WHERE" & varWhere
>
> > > If Right(varWhere, 5) = " AND " Then
> > > varWhere = Left(varWhere, Len(varWhere) - 5)
> > > End If
> > > End If
>
> > > BuildFilter = varWhere
>
> > > End Function
>
> > > And behind the cmdSearch_Click()
> > > Private Sub cmdSearch_Click()
>
> > > Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> > > BuildFilter
>
> > > Me.subfrmSearch.Requery
>
> > > End Sub
>
> > > PLEASE HELP
>
> > To add to what OldPro said. In your statement,
>
> > Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qrySearch WHERE " &
> > BuildFilter
>
> > modify it as such and create a temporary table that you can export to
> > Excel.
>
> > docmd.runsql "select * into tblTemp from qrySearch where " &
> > buildfilter
>
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
> > "tblTemp", strFile
>
> > where strFile is the path of the file you want the data to go into.
> > For example, you can have
>
> > strFile = "c\Temp\temp.xls"
>
> > Good luck!
>
> > Dorothy
> > financialreporting@googlegroups.com- Hide quoted text -
>
> - Show quoted text -

If you look at the SQL View of your query, you should be able to copy
it almost verbatim into a code query that includes whatever filter you
want and appends records to a table. It would look something like
this:
sQry="INSERT INTO tblTemp (fld1,fld2,fld3) SELECT fld1,fld2,fld3 FROM
tblSource WHERE fld1 = ' " & txtName & " ' ;"

Then you can call it in your command button with something like this:

Dim sQry as String
Dim db as dao.database
sQry="INSERT INTO tblTemp (fld1,fld2,fld3) SELECT fld1,fld2,fld3 FROM
tblSource WHERE fld1 = ' " & txtName & " ' ;"
set db=currentdb()
db.execute sQry
DoCmd.TransferSpreadsheet acExport, 8, "tblTemp", "C:\Spreadsheet.xls"

In this example, I assumed a filter based on alpha-numeric data and
therefore added single quotes in the WHERE clause. True numeric data
would only work without quotes, and dates need to be delimited by the
# sign in place of the single quotes. The field list
"(fld1,fld2,fld3)" doesn't need to have the same names as "SELECT
fld1,fld2,fld3", but it does need to have the same number of fields
and be in the same order. Also, each of the fields needs to be of the
same type as its corresponding field.