Ahhh, got it. THANK YOU!...and thanks for clearing/cleaning that up...I see how you did the loop and got it to work inside the SQL....I added cfsqltype="CF_SQL_DATE" to the two date fields and it magically worked. THANK YOU AGAIN.
-------------------------------------------------------- Eric J. Hoffman Market Connections 2081 Industrial Blvd. Stillwater MN 55082 http://www.marketconnections.us T: 651.207.1526 F: 651.207.1536 M: 952.210.9060 E: [EMAIL PROTECTED] This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. ________________________________ From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wed 3/23/2005 2:50 PM To: CF-Talk Subject: Re: divide by zero error Eric J. Hoffman wrote: <cfquery name="getcust#i#" datasource="#heinz.dsn#"> SELECT DISTINCT customer.id as cid, customer.business, customer_orders.id, SUM (customer_orders_prod.qty) as totunits FROM customer_orders JOIN customer on customer.id = customer_orders.customer_id JOIN customer_contact on customer.id = customer_contact.customer_id LEFT OUTER JOIN customer_orders_prod on customer_orders.id = customer_orders_prod.order_id WHERE customer_orders.orderdate BETWEEN <cfqueryparam value="#DateAdd('D', -1, form.start1)#"> AND <cfqueryparam value="#DateAdd('D', 1, form.end1)#"> AND customer_orders_prod.product_id = <cfqueryparam value="#i#"> <cfif form.lowcap GT 0 AND form.highcap GT 0> AND customer_orders_prod.qty BETWEEN <cfqueryparam value="#form.lowcap#"> AND <cfqueryparam value="#form.highcap#"> </cfif> <cfif form.lowcap GT 0 AND form.highcap EQ ''> AND customer_orders_prod.qty >= <cfqueryparam value="#form.lowcap#"> </cfif> <cfif form.lowcap EQ '' AND form.highcap GT 0> AND customer_orders_prod.qty <= <cfqueryparam value="#form.highcap#"> </cfif> <cfif form.state NEQ ''> AND customer.state = <cfqueryparam value="#form.state#"> </cfif> <cfif form.area neq ''> AND ( 1=0 <cfloop list="#form.area#" index="m"> OR customer_contact.phone LIKE <cfqueryparam value="%#trim(m)#%"> </cfloop> ) </cfif> <cfif form.zip NEQ ''> AND ( 1=0 <cfloop list="#form.zip#" index="p"> OR customer.zip LIKE <cfqueryparam value="%#trim(p)#%"> </cfloop> ) </cfif> AND (business NOT LIKE '%HSR%' AND business NOT LIKE '%OSR%' AND business NOT LIKE '%HAS%' AND business NOT LIKE '%HAM%') AND customer.category <> 5 GROUP BY customer_orders.id, customer.id, business </cfquery> Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:199820 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
