Re: Pulling results from 1 Text field and 2 Select Lists

Joe Rinehart Sat, 07 Aug 2004 15:08:32 -0700

Hey Steve,

At first glance, you need to partition off the seperate conditional
queries in your clause.

This should help:

<CFQUERY NAME="LocateLawyer" DATASOURCE="Lawyer">
SELECT *
FROM tblLawyers
WHERE 0=0


<CFIF FORM.LName IS NOT "">
     AND LName LIKE '%#FORM.LName#%'
</CFIF>

<CFIF FORM.County IS NOT "">
     AND (
County1 LIKE '%#FORM.County#%'
        OR County2 LIKE '%#FORM.County#%'
        OR County3 LIKE '%#FORM.County#%'
        OR County4 LIKE '%#FORM.County#%'
     )

</CFIF>


<CFIF FORM.Practice IS NOT "">
     AND (
Practice1 LIKE '%#FORM.Practice#%'
        OR Practice2 LIKE '%#FORM.Practice#%'
        OR Practice3 LIKE '%#FORM.Practice#%'
        OR Practice4 LIKE '%#FORM.Practice#%'
     )
</CFIF>
</CFQUERY>

You may also want to use lookup tables to keep track of practices and
counties, and tables to relate lawyers to them.  This would let you
relate as many practices or counties to an individual lawyer as you'd
like, not limiting to 4.

Cheers,

Joe

----- Original Message -----
From: Steve Sawyer <[EMAIL PROTECTED]>
Date: Sat, 7 Aug 2004 14:52:44 -0700
Subject: Pulling results from 1 Text field and 2 Select Lists
To: CF-Talk <[EMAIL PROTECTED]>

I am trying to allow for selecting from a text box and two drop down lists.
An individual could type in any part of a Last Name and select one choice
from each of two Select lists.  The result set should determine only those
results that apply to combination of each of the choices.

The fields, LName, County, and Practice are all in the same table.
Additionally, County references 4 individual fields: County1, County2,
County3, and County4.  The same applies to Practice.

I want someone to be able to locate a lawyer based on part of the last name,
which county they're practicing in, and what their field of practice is.
Each lawyer is listed by 4 different County fields and 4 different Practice
fields.

The results are coming back accurately when I submit one field but not when
I submit two or more.

Any help would be appreciated.



<CFQUERY NAME="LocateLawyer" DATASOURCE="Lawyer">

SELECT *

FROM tblLawyers

WHERE 0=0



<CFIF FORM.LName IS NOT "">

     AND LName LIKE '%#FORM.LName#%'

</CFIF>



<CFIF FORM.County IS NOT "">

     AND County1 LIKE '%#FORM.County#%'

             OR County2 LIKE '%#FORM.County#%'

             OR County3 LIKE '%#FORM.County#%'

             OR County4 LIKE '%#FORM.County#%'

</CFIF>



<CFIF FORM.Practice IS NOT "">

     AND Practice1 LIKE '%#FORM.Practice#%'

             OR Practice2 LIKE '%#FORM.Practice#%'

             OR Practice3 LIKE '%#FORM.Practice#%'

             OR Practice4 LIKE '%#FORM.Practice#%'

</CFIF>

ORDER BY LName

</CFQUERY>________________________________

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

Re: Pulling results from 1 Text field and 2 Select Lists

Reply via email to