Query Question

[still_smiling1]

It's been awhile since I've had to create one of these and would grealy appreciate a little assistance with the IsDefined in my statement. Here is my code. The ones I'm having issues with is the last two statments that have to do with the date ranges. When I take those two out everything else works perfect. I put those in and I get an empty result set on every type of query. I know there is a suit_status of 5 so data should show up and it's not when those two statements are in. Thanks.

<cfquery name="GetResults" datasource="DEV">

SELECT sid, User_ID, DEALER_NAME, ORDER_DATE, PO, CUSTOMER_NAME, SALESMAN, Expected_delivery_date, rush_btn, IS_FLAG_ORDER, suit_status

    FROM suitorder

    WHERE SUIT_STATUS = #FORM.SUIT_STATUS#

    <cfif IsDefined("FORM.customer_name")>

    AND customer_name LIKE '%#FORM.customer_name#%'

    </cfif>

    <cfif IsDefined("FORM.factory_order")>

    AND factory_order LIKE '%#FORM.factory_order#%'

    </cfif> 

<cfif IsDefined("FORM.order_date")>

    AND ORDER_DATE BETWEEN <cfqueryparam value="#FORM.order_date#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#FORM.order_date2#" cfsqltype="cf_sql_date">

    </cfif>

    <cfif IsDefined("FORM.expdate2")>

    AND Expected_delivery_date BETWEEN <cfqueryparam value="#FORM.expdate2#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#FORM.expdate3#" cfsqltype="cf_sql_date">

    </cfif>

</cfquery>

[Dan_Bracuk · Accepted solution]

I suspect that isDefined(your date field) will always return true.  That's going to cause problems for you eventually.  isDate() is probably a better choice.

For the problem you state, are you sure that the order date really is between the values you submitted on the form?

[still_smiling1]

Thank you Dan, that was it!