Yeh, I don't think you have any choice but to convert to varchar if you need to do the grouping (pls somone correct me if I'm wrong).
I would run some sql to check the biggest note and convert the text field into a varchar of reasonable size relative to that (hoping that it is less than 8000): SELECT Max(Len(myTextField)) FROM myTextFieldTable Personally I would update the field in the table if it was possible, rather than doing a cast in the SQL select statement(s). Regards, Dominic On 13/11/2007, Daniel Roberts <[EMAIL PROTECTED]> wrote: > > The table is more like this, which I might not have expressed clearly > earlier. > > > 1,10,John,note1 > 2,10,Jack,note1 > 3,10,Jean,note1 > 4,20,David,note2 > 5,20,Duncan,note2 > 6,30,Sean,note3 > > The text fields (notes in this case) are duplicated for records in a > certain group. This is very kludgy but unfortunately how it was setup some > dozen years ago. I had CF code using Access but want to move to MSSQL until > we can overhaul the entire app. > > The query is attempting to get unique text fields based on other fields > that would indicate such. For example group on the first 3 columns and > select the last text column. > > I think Aaron's idea will work for me. I think the text fields would fit > into a large varchar without a problem. It may even be an option to update > the text fields in the table structure to a large varchar if not change of > hitting the page size limit. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293204 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
