Not Null - 0

Hi,

How can I ensure that a query is not null:

SELECT SUM(appold.reccost) AS `Sum`, (SUM(appold.reccost) *0.175) AS `SumVat`
FROM appold
WHERE ((appold.build_name = '1ASAM') AND (appold.monthentry = MMColParam))



if there are no entries in the DB I get a problem when I format the fields but also I need to show that the values are 0 not empty.

If you add a count you get 0 but not sum?

Is there a way around this?

Thanks,

Sanjay

yes, count(*) is the only aggregate that does not go null when there are no values

what you want is COALESCE

SELECT COALESCE(SUM(appold.reccost),0) AS `Sum`
, COALESCE(SUM(appold.reccost),0) *0.175 AS `SumVat`


rudy

Hi rudy,

I tried pasting the query into Dreamweaver but it displays an error:

[Microsft][ODBC Microsoft Access Driver] Undefined function 'COALESCE' in expression

Can I not use that in Access?

Thanks,

Sanjay

no, you can't

COALESCE is a standard sql function, and since you did not say which database you were using...

furthermore, your backticks suggested that your database is mysql, which does support COALESCE

SELECT IIF(ISNULL(SUM(appold.reccost)),0,SUM(appold.reccost)) AS "Sum"
, IIF(ISNULL(SUM(appold.reccost)),0,SUM(appold.reccost)) *0.175 AS "SumVat"

Rudy,

Sorry for not making it clear what DB I was using.

The code you have given works great.

Thanks

Sanjay

thanks for the followup, glad it worked

one nice thing about sql standards -- there are so many to choose from

Not a problem, is there a site to find out more - I never knew about IIF command?

Thanks,

Sanjay

i would start with the Help file in access

after that, i would go to google -- here's a site that came up on the first page of a search for "IIF+and+Access":

http://www.wardcameron.com/FrontPag...s/iifStatement/


rudy