Inserting zero into field when query returns no value.

[Guest]

Hi,

I have a transactional table called Tickets. It has fields such as Date,
Item, Amount, and Type. I have a query called ProductRevenueQuery1 which
runs against this Tickets table and is designed to list all items sold for
the week. I use a date restrictor to do this along with a Type=Product.
However, there are some weeks when there are no items sold.

I have a second query called ProductRevenueQuery2. This sums the Amount
column for Query1. In addition, I have added a fields called Week_Of and
used the value of DateValue(Now())-Weekday(DateValue(Now()),2)-6 for it.
What this query give me is sales for last week and the date for last monday.
So if there is a value, the date field will be 5/9/05 and the value will be
$110 or something like that. But if there is no sales, nothing is returned.
I still want a value, even if it is zero. I would like Query2 to return the
value of 5/9/05 in the date field and 0 in the SumOfAmount field.

Can someone help?

Thanks,

 

[Jeff Boyce]

Are you using the Nz() function in your queries?

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,

Based on the information in my note how would I structure the NZ. I am not
that familiar with this function.

Thanks,