totals query does not show zero and currency format (1 Viewer)

Hi, I have simple totals query. I take my data from another query and use Sum to total it.

I need to display 0 if nothing to total.
I need to format as currency, tried to select it from property sheet does not work.

I have made it work by editing SQL:

SELECT CCur(Nz(Sum(my_query_ name.SumOf_myfieldname),0)) AS sum of _myfieldname
FROM my_query_ name_group;

I had to use CCur() and Nz(), why normal way expr1: nz([sumof_myfieldname]);0) does not work? It's shown as expression now.

Hi -

Try changing:

nz([sumof_myfieldname]);0)

to:

nz([sumof_myfieldname],0)

Bob

raskew said:

Hi -

Try changing:

nz([sumof_myfieldname]);0)

to:

nz([sumof_myfieldname],0)

Bob

Click to expand...

I entered:
total: Nz([sumof_myfieldname];0)

but no zero if my value is null.

your code genreates this sql:
SELECT Sum(Nz([SumOf_myfieldname],0)) AS total
FROM my_query_ name_group;

my working sql but shown as expression:
SELECT CCur(Nz(Sum(my_query_ name_group.SumOf_myfieldname),0)) AS total
FROM my_query_ name_group;

I would like to avoid using expression

any comments?