[AccessD] Re: need help with filter code

Hello Robert and Rocky,

thanks for help on the filter code.
I will try both and see which can serve my needs.

Pedro



----- 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
>