Values only show up occasionally

[Robbro]

I have a report I am trying to get access to perform with the following form
Customer Product 1
Product 2
Product 3
Subtotal
Returns & Rebates Ret
Net Sales Subtotal-Ret

It works just fine for customers with values for Ret, but fur customers with
no returns, nothign shows up in Net Sales. I've tried an IIf statement to
see if I can get it to carry the subtotal down for those customers but still
nothing. I'm new to access and probably stuck in spreadsheet think, but I
need net Sales to have a value, because I plan on using that value later in
calculations and I want it to show up on my report also.

Thanks

 

[John Spencer]

You need to post your expression that you are using for Net Sales.

You probably need something like the following as the control's source expression.

=Sum(Amount)-NZ(Sum(Returns),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Did you try the Nz function?

 

[Jerry Whittle]

Are the returns in a different table? If so the query probably has more than
one table.

You'll need to double-click on the line between the two tables in the query
and select the second option when the dialog box appears. You need a left
join so that records will still show up when there isn't a ret.

The first option is an inner join which means both tables must have a
matching record for any record to be returned.

 

[Jerry Whittle]

I just had a second, probably better, thought.

If the Ret is a null, then subtracting it from the Subtotal will produce a
null.

Try surrounding the Ret field with the NZ function. If it's a Null value,
you can use NZ to change it to 0. It would look something like below.

NZ([Ret],0)

 

[Robbro]

That was exactly it, NZ fixed it for me. Lots of simple things I still dont
know about Access I guess...

I just had a second, probably better, thought.

If the Ret is a null, then subtracting it from the Subtotal will produce a
null.

Try surrounding the Ret field with the NZ function. If it's a Null value,
you can use NZ to change it to 0. It would look something like below.

NZ([Ret],0)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a report I am trying to get access to perform with the following form
Customer Product 1
Product 2
Product 3
Subtotal
Returns & Rebates Ret
Net Sales Subtotal-Ret

It works just fine for customers with values for Ret, but fur customers with
no returns, nothign shows up in Net Sales. I've tried an IIf statement to
see if I can get it to carry the subtotal down for those customers but still
nothing. I'm new to access and probably stuck in spreadsheet think, but I
need net Sales to have a value, because I plan on using that value later in
calculations and I want it to show up on my report also.

Thanks