default values for queries

[mike]

I cannot find a way to assign a default value in a query
or for a report generated from the query. I am trying to
generate an equipment profit report showing the income,
expenses, and gross profit by equipment. I need month,
year, and life-to-date totals. My problem is if there is
no repair detail yet on a new piece of equipment, that
field is NULL. If it's Null, then my GP will not
calculate. Same is true if a new piece of equipment
hasn't yet generated income, I have blanks in income and
gp. I want a default of 0 if there is no detail to
summarize for that field. Any ideas?
thanks,
-mike

 

[Allen Browne]

Use Nz() to tell Access to use a zero for null.

In SQL View (view menu), this would look something like this:
SELECT Sum([MyField]) As SumOfMyField, ...
so change it to:
SELECT Nz(Sum([MyField]),0) As SumOfMyField, ...

 

[Ian]

ooh I cound kiss you Allen

Why oh why have they changed from the usual COALESCE
function?

oh well, it all works now thatnks to you!

-----Original Message-----
Use Nz() to tell Access to use a zero for null.

In SQL View (view menu), this would look something like this:
SELECT Sum([MyField]) As SumOfMyField, ...
so change it to:
SELECT Nz(Sum([MyField]),0) As SumOfMyField, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I cannot find a way to assign a default value in a query
or for a report generated from the query. I am trying to
generate an equipment profit report showing the income,
expenses, and gross profit by equipment. I need month,
year, and life-to-date totals. My problem is if there is
no repair detail yet on a new piece of equipment, that
field is NULL. If it's Null, then my GP will not
calculate. Same is true if a new piece of equipment
hasn't yet generated income, I have blanks in income and
gp. I want a default of 0 if there is no detail to
summarize for that field. Any ideas?
thanks,
-mike

.