Form results using Access - SQL statements

[Mark Leder]

I'm really green at this, so if this not appropriate for this forum, point

me in the right direction.

I work in Cold Fusion and we are performing a query results from a search

form. There are 3 textfields in the form (firstname, lastname, and company

name) and one list field (named "topics") from which the end user can pick

multiple selections.

Here's the IF statements for the results page:
-------------------------------------------------

<cfquery name="rsMembers" datasource="ohiospkr">
SELECT * FROM members
WHERE member_id = member_id

<CFIF firstName IS NOT "">
AND firstName LIKE '#firstName#%'
</CFIF>

<CFIF lastname IS NOT "">
AND lastname LIKE '#lastname#%'
</CFIF>

<CFIF companyname IS NOT "">
AND companyname LIKE '#companyname#%'
</CFIF>

<CFIF topics IS NOT "">
OR ta IN ('#Replace(topics, ",", "', '", "all")#')
OR tb IN ('#Replace(topics, ",", "', '", "all")#')
OR tc IN ('#Replace(topics, ",", "', '", "all")#')
OR td IN ('#Replace(topics, ",", "', '", "all")#')
OR te IN ('#Replace(topics, ",", "', '", "all")#')
OR tf IN ('#Replace(topics, ",", "', '", "all")#')
OR tg IN ('#Replace(topics, ",", "', '", "all")#')
OR th IN ('#Replace(topics, ",", "', '", "all")#')
OR ti IN ('#Replace(topics, ",", "', '", "all")#')
OR tj IN ('#Replace(topics, ",", "', '", "all")#')
</CFIF>

ORDER BY lastname, firstName
-------------------------------------

The firstname, lastname, and companyname all work OK by themselves or in

combination. If they are blank, we retreive and output all records - so

far so good. This is an MS Access DB.

However, the "topics" list need to be able to look at (loop through) a
series of 10 fields (named ta, tb, tc, and so on) and return any instances

matching the topics selected from the list. Or if no topics are selected,

just return all records.

I've tried lots of combinations/variations to this with no luck. I've been
stumped for several weeks now on this.

Any help from an SQL pro would be greatly appreciated.

[Scott]

"Mark Leder" <mleder@mindspring.com> wrote:
>
>I'm really green at this, so if this not appropriate for this forum, point
>
>me in the right direction.
>
>I work in Cold Fusion and we are performing a query results from a search
>
>form. There are 3 textfields in the form (firstname, lastname, and company
>
>name) and one list field (named "topics") from which the end user can pick
>
>multiple selections.
>
>Here's the IF statements for the results page:
>-------------------------------------------------
>
><cfquery name="rsMembers" datasource="ohiospkr">
>SELECT * FROM members
>WHERE member_id = member_id
>
><CFIF firstName IS NOT "">
>AND firstName LIKE '#firstName#%'
></CFIF>
>
><CFIF lastname IS NOT "">
>AND lastname LIKE '#lastname#%'
></CFIF>
>
><CFIF companyname IS NOT "">
>AND companyname LIKE '#companyname#%'
></CFIF>
>
><CFIF topics IS NOT "">
>OR ta IN ('#Replace(topics, ",", "', '", "all")#')
>OR tb IN ('#Replace(topics, ",", "', '", "all")#')
>OR tc IN ('#Replace(topics, ",", "', '", "all")#')
>OR td IN ('#Replace(topics, ",", "', '", "all")#')
>OR te IN ('#Replace(topics, ",", "', '", "all")#')
>OR tf IN ('#Replace(topics, ",", "', '", "all")#')
>OR tg IN ('#Replace(topics, ",", "', '", "all")#')
>OR th IN ('#Replace(topics, ",", "', '", "all")#')
>OR ti IN ('#Replace(topics, ",", "', '", "all")#')
>OR tj IN ('#Replace(topics, ",", "', '", "all")#')
></CFIF>
>
>ORDER BY lastname, firstName
>-------------------------------------
>
>The firstname, lastname, and companyname all work OK by themselves or in
>
>combination. If they are blank, we retreive and output all records - so
>
>far so good. This is an MS Access DB.
>
>However, the "topics" list need to be able to look at (loop through) a
>series of 10 fields (named ta, tb, tc, and so on) and return any instances
>
>matching the topics selected from the list. Or if no topics are selected,
>
>just return all records.
>
>I've tried lots of combinations/variations to this with no luck. I've been
>stumped for several weeks now on this.
>
>Any help from an SQL pro would be greatly appreciated.

Mark,
I had some trouble with a dynamic IN statement and the only way I found to
get it to work was to include at least one other bogus variable in the IN
statement.

OR tj IN ('VR549','#Replace(topics, ",", "', '", "all")#')

If you would email the CF debug output of a query I might be able to help.


Scott