Trying to follow your code here I think the problem may be that you are trying to use a ColdFusion expression as part of your SQL statement and you can't do that.
All CF inside the cfquery tag is evaluated FIRST given what variables have been defined already in the page; then the result of that is handed off to the database for processing. The code as you pasted it requires that a ColdFusion variable called "contractinfo.months" to be defined. I *think* you are expecting the value of the months column in the contractinfo table for each record to be evaluated. I'm a little confused though since your original post showed "contractinfo.months", but your subsequent post shows code with only "months" in the listfind. If you wish to return records in which the value for the months column of the contractinfo table is found in the list stored in your form.Monthselect select variable, then you want to do: WHERE contractinfo.months in (<cfqueryparam value="#form.Monthselect#" cfsqltype="CF_SQL_VARCHAR" separator="/" list="Yes">) Which will result in this equivalent SQL being passed to the DB: WHERE contractinfo.months in (1,2,3,4,5) Does that help? ~Brad -----Original Message----- From: Scott Kuo [mailto:[EMAIL PROTECTED] Sent: Friday, December 21, 2007 4:11 PM To: CF-Talk Subject: Re: Some troubles with my Query <CFQUERY name="viewlist" dataSource="Data171"> SELECT contractinfo.* , Buildings.* , Freqtype.* , matypes.* , groups.* , servicetypes.* FROM ((((contractinfo Left Join Buildings on contractinfo.buildingcon = buildings.bid) Left Join Groups on Buildings.group1 = groups.GID) Left Join matypes on contractinfo.matype = matypes.matkey) Left join freqtype on contractinfo.freqperyear = freqtype.timesperyear) Left join servicetypes on contractinfo.servicetype = servicetypes.servicekey WHERE #Listfind(months, form.Monthselect, "/")# > 0 </CFQUERY> The From is a bit complicated but not bad. It just joining 6 tables together. The months is inside contractinfo. >> "Element MONTHS is undefined in CONTRACTINFO" in my query. > >If you have a question about your code, you should post your code. Without >seeing it, I can't say for sure what's causing your problem. > >That said, you can't refer to a CF value derived from a query within that >same query. > >Dave Watts, CTO, Fig Leaf Software >http://www.figleaf.com/ > >Fig Leaf Software provides the highest caliber vendor-authorized >instruction at our training centers in Washington DC, Atlanta, >Chicago, Baltimore, Northern Virginia, or on-site at your location. >Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295286 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
