Qty. On Hand

[Denise]

I have a database for inventory. I'm trying to get a Qty.
on hand total on the form. What it has to equal to is all
records that aren't checked as Sold. so...... Qty on
hand = all records not sold.
Someone help please.

 

[Steve Schapel]

Denise,

I assume that the Sold field is a Yes/No data type? If so, I think
something like this, as the Control Source of an unbound textbox in the
Form Footer...
=Sum([Qty]*IIf([Sold]=-1,1,0))
or, simpler but perhaps less intuitive...
=Abs(Sum([Qty]*[Sold]))

 

[Guest]

Steve,
In my form design view, I added an Unbound text box in the
footer section of my form. Cut and pasted the formula
that you gave me, then tested it with several items some
marked with sold and some not. It still doesn't give me a
total. It remains blank. And yes, the "sold" is a yes/no
check box. I know this may be a very simple process, but
it sure is kicking my bottom. any suggestions?

-----Original Message-----
Denise,

I assume that the Sold field is a Yes/No data type? If so, I think
something like this, as the Control Source of an unbound textbox in the
Form Footer...
=Sum([Qty]*IIf([Sold]=-1,1,0))
or, simpler but perhaps less intuitive...
=Abs(Sum([Qty]*[Sold]))

--
Steve Schapel, Microsoft Access MVP

I have a database for inventory. I'm trying to get a Qty.
on hand total on the form. What it has to equal to is all
records that aren't checked as Sold. so...... Qty on
hand = all records not sold.
Someone help please.

.

 

[Steve Schapel]

Denise,

Blank? You mean completely blank? Not showing #Name? or #Error? And
Qty is the name of your quantity field? And there are numbers in this
field? And Sold is the name of your sold field? And the calculation
expression is in the Control Source of the the textbox in the footer?
Hmmm. I don't know!

 

[Guest]

Steve,
Yes, now I have a #Name? in the box when I'm in Form View.

 

[Steve Schapel]

Denise,

Well, that's better than blank!

It is important that the name of the unbound textbox is not the same as
the name of any field in the form's recordsource. And it is important
that the fields referred to in the expression are in the form's
recordsource, and that they are correctly spelt as Qty and Sold. And it
is important that there are no controls on the form called Qty or Sold,
unless thay are the controls bound to the Qty and Sold fields. Can you
please check these.