[AccessD] Re: need help with filter code

Robert L. Stewart rl_stewart at highstream.net
Tue Feb 17 12:34:27 CST 2004


Here are the steps that I would take:

1.  Create a base query with all the fields you want to display.
         i.e. sqry_FilterQuery_0

2.  Do a save as and save the same query with an _1 on the end instead of _0.

In the code behind the OnClick event of your command button place the 
following:

dim db as dao.database
dim qdf as dao.querydef
dim strSql as string
dim strWhere as string
set db=currentdb()
set qdf=db.querydefs("base query name")
' removes the ; from the end of the query string
strsql = mid(qdf.sql,1,len(qdf.sql)-3)
strwhere = " WHERE ("
' build the string for each of the 6 columns you want to search in
strwhere = strwhere & "(column1 Like '*" & me!textbox & "*') OR"
strwhere = strwhere & "(column2 Like '*" & me!textbox & "*') OR"
strwhere = strwhere & "(column3 Like '*" & me!textbox & "*') OR"
strwhere = strwhere & "(column4 Like '*" & me!textbox & "*') OR"
strwhere = strwhere & "(column5 Like '*" & me!textbox & "*') OR"
strwhere = strwhere & "(column6 Like '*" & me!textbox & "*'));"
set qdf = db.querydefs("_1 query name")
qdf.sql = strsql & strwhere
db.close
set db=nothing
' enter the code here to open either the _1 query or a form/report
' based on the _1 query

By doing it this way, you do not have to fool around with the filter property.

Robert

Replace the column1-6 with the names of the columns that you want to search 
in.  Replace me!textbox with the name of the text box you are entering the 
criteria in.  Replace "base query name" with the name of the _0 
query.  Replace "_1 query name" with the name of the _1 query.

At 11:57 AM 2/17/2004 -0600, you wrote:
>Date: Tue, 17 Feb 2004 10:35:01 +0100
>From: "Pedro Janssen" <pedro at plex.nl>
>Subject: [AccessD] need help with filter code
>To: <AccessD at databaseadvisors.com>
>Message-ID: <003401c3f539$727cac20$f7c581d5 at pedro>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>Hello Group,
>
>i have 6 patients fields in which i want to do a search on their names.
>I don't want to use the existing filter in access, but search with a
>commandbutton and a textbox.
>I can use the following code with 6 cmdbuttons and 6 textboxes,
>but that doesn't work in practice.
>What i want is one cmdbutton and one txtbox that searches true all 6 fields
>Can you help me on this?
>
>
>Private sub  cmdsearch1_Click()
>Me.Filter = "qryPatientData.Patient1ID Like'*" & Me.txtboxSearch1 & "*'"
>Me.FilterOn = True
>Me.Requery
>Me.Refresh
>
>End Sub





More information about the AccessD mailing list