Bryan, 2 quick comments:
1. "NZ" is a Microsoft Access function. It's basically just an IIf saying if the field is not null, pass the contents of the field, but if the field is null, pass a zero. 2. The default join type is an "inner" join where the field exists in both tables. Look up "outer" joins where the field is optional in one of the tables. HTH. -Ben -----Original Message----- From: Langford, Bryan [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 4:10 PM To: CF-Talk Subject: Dealing with nulls while adding two columns together Hey everyone, I have two separate views that pull numeric data from a table using SQL. I am looking to add the two together in another view before bringing it back with cold fusion for display. My problem is that if either table has a null value data field where the join fields are equal, the request comes back blank. So I have tried "coalesce" and even went as far as trying an old trick I learned when I developed Access databases which was to use "NZ(table.field)+NZ(table2.field2)" but as expected, that errored out. Does anyone have some suggestion I might try to correct this problem and if so, what are they? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
