[AccessD] Re: need help with filter code

Hello Robert (and others),

i used your code to filter on multiple fields.
I works, but in the query, not in the form.
When i enter a value in the txtbox and click the command button,
nothing happens. Looking in the query_1 i see that the Like ... Or statement
is placed in the fields. Looking in the queryview it also filters the right
values.
Then returning to the form and looking in its view, then it is showing the
exact
values. I tried to requery and refresh in the code, but nothing changed.
Do you know how this is possible?

Pedro Janssen


----- Original Message -----
From: "Robert L. Stewart" <rl_stewart at highstream.net>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, February 17, 2004 7:34 PM
Subject: [AccessD] Re: need help with filter code


> 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
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>