Form results using Access - SQL statements


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Form results using Access - SQL statements

  1. #1
    Mark Leder Guest

    Form results using Access - SQL statements


    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.

  2. #2
    Scott Guest

    Re: Form results using Access - SQL statements


    "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




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center