Flexible Criteria Search redux

this action page query mutiple drop-down selection works 2 choices- when third introduced, ignores it:

 

<cfquery name="search_flexible_criteria" datasource="#request.basedsn#">

select

mo.order_id,

mo.order_number,

mo.order_property_type_id,

mo.order_order_type_id,

mo.order_report_type_id,

mo.order_client_id,

mo.order_appraiser_id,

mo.order_status_id,

mo.order_rush_status_id,

mo.order_property_street,

mo.order_property_city,

mo.order_create_date,

 

ls.order_status_id,

ls.order_status,

 

 

lc.client_id,

lc.client_company,

 

la.appraiser_id,

la.appraiser_fname,

la.appraiser_lname,

 

lo.order_type_id,

lo.order_type,

 

lp.property_type_id,

lp.property_type,

 

lr.report_type_id,

lr.report_type,

 

lrs.rush_status_id,

lrs.rush_status

 

 

from  main_orders mo

 

 

       

        left join lookup_order_status ls

        on mo.order_status_id = ls.order_status_id

       

        left join lookup_clients lc

        on mo.order_client_id = lc.client_id

       

       

        left join lookup_appraisers la

        on mo.order_appraiser_id = la.appraiser_id

       

        left join lookup_order_type lo

        on mo.order_order_type_id = lo.order_type_id

       

        left join lookup_property_type lp

        on mo.order_property_type_id = lp.property_type_id

       

        left join lookup_report_type lr

        on mo.order_report_type_id = lr.report_type_id

       

       

        left join lookup_rush_status lrs

        on mo.order_rush_status_id = lrs.rush_status_id

       

        1 = 1

 

 

 

 

<cfif structkeyexists(form, "startdate") , structkeyexists(form, "enddate")  , form.startdate not  "" , form.enddate not  "">

 

and (order_create_date between #createodbcdate(form.startdate)# , #createodbcdate(form.enddate)#)</cfif>

 

<cfif structkeyexists(form, "order_client_id") , form.order_client_id neq 0>

 

 

and client_id  = #form.order_client_id# </cfif>

 

 

<cfif structkeyexists(form, "order_appraiser_id") , form.order_appraiser_id neq 0>

 

 

and  appraiser_id  = #form.order_appraiser_id#</cfif>

 

 

<cfif structkeyexists(form, "order_status_id") , form.order_status_id neq 0>

 

and mo.order_status_id = #form.order_status_id#</cfif>

 

<cfif structkeyexists(form, "order_rush_status_id") , form.order_rush_status_id neq 0>

 

and order_rush_status_id = #form.order_rush_status_id#</cfif>

 

order order_create_date desc

 

</cfquery>  

 

thanks carl getting far- hair-pulling, hopefully, stop soon.

 

thank taking time read this.

 

norman

norman,

did suggested in other thread , test query in ssms? isolate whether there problem sql being generated or cf logic.

 

-carl v.

More discussions in Advanced Techniques

adobe