apply filter in transfertext method ???

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

Dear helper,

I used “Docmd.transfertext” to export a query result to a
text file. My code is as follows:

DoCmd.TransferText acExportDelim, , "customer -
testing", "i:\accnting\HI mailing database\testing1.txt", -
1

My question is how to add a filter in the query via code.
I have a form to display 5 filter fields, which the user
can select from. The user can use one field or any
combination. I tried to hardcore all the filters in the
query. It only works when the user enters all 5 fields.
If the user has any empty field, the query result will be
empty. I have no problem to capture the selected filters
as a string but I do not know how to apply the string
into the above code “Docmd.transfertext………”

Please help. I am very appreciated

Thank you.
 
I think what you need to do is build a select query in code.

Then use that query in the Transfer Text method.

The code should be something like this:

Dim strSelect As String
Dim strWhere As String
Dim strFrom As String
Dim strFilter As String
Dim db As DAO.database
Dim qd As DAO.QueryDef
Dim intFilters As Integer
Dim strFieldNames(5) As String
Dim k As Integer
Dim strSQLText As String
strSelect = "SELECT tblSomeTable.*"
strFrom = "FROM tblSomeTable"
strWhere = "Where "
intFilters = 0
strFieldNames(1) = "Field1"
strFieldNames(2) = "Field2"
strFieldNames(3) = "Field3"
strFieldNames(4) = "Field4"
strFieldNames(5) = "Field5"
For k = 1 To 5
strFilter = Nz(Me.Controls("cboFilter" & Trim(k)), "")
If Len(strFilter) > 0 Then
If intFilters = 0 Then
strWhere = strWhere & "[tblSomeTable].[" & strFieldNames(k)
& "]= '" & strFilter & "'"
intFilters = intFilters + 1
Else
strWhere = strWhere & " And [tblSomeTable].[" &
strFieldNames(k) & "]= '" & strFilter & "'"
intFilters = intFilters + 1
End If
End If
Next
strSQLText = strSelect & vbCrLf & strFrom & vbCrLf & strWhere
Set db = CurrentDb
DoCmd.DeleteObject acQuery, "qryExport" 'This will raise an error if the
query does not exist
Set qd = db.CreateQueryDef("qryExport", strSQLText)


--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/
 
build the where clause by checking for existance of info in each field
insert the function MyWhereClause in the "where" portion of the query string
Private Function MyWhereClause() as string
Dim tmpVar as String
Dim fm as Object
MyWhereClause = ""
Set fm = Me
for each field in fm
if not isnull(field) then
tmpVar = "field.name =" & "'" & field & "' And "
next field
' remove the trailing " And "
tmpVar = left(tmpVar, len(tmpVar)-5)
If not IsNull(tmpVar) then MyWhereClause = "Where " & tmpVar
End Function
docmd.runsql "Your SQL statement" & MyWhereClause & ";"
I am doing this from memory so syntax might need corrections
THT
 
Back
Top