> I'd been trying to come up with a way to do this, but the columns with a > "0" in them are throwing me off, since "0" isn't a valid date value.
You could use CASE or IF to fill in dummy values for those zeroes, but then, if you substituted 1 (January) for the month for example, it wouldn't be found in a date range search from Feb - April for example. If a record only has the year entered, for example 2007, I suppose you'd want to return it for any search that had 2007 in the date range. So maybe you could do a conditional check for that in the subquery, and then return any of those records that match the year -- something like CASE WHEN day = 0 and month = 0 and year <> 0 then 'true' else 'false' END AS 'matchonlyyear' Then maybe do a query of queries or something to check for those "year only" records. All I can say is you are going to have to get super creative and pull out all the stops. -- Josh ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:301968 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
