Textbox giving an #Error message

  • Thread starter Thread starter Sean Setter
  • Start date Start date
S

Sean Setter

I need some help figuring out why one particular text box is giving me
"#Error". I have form with a nested subform that displays all of the
records matching information from the parent form. Nothing weird
there, except for the textbox in the footer of the subform. The box is
supposed to display totals from the records from the subform's Details,
which worked fine when I first started this project using
"=Sum([Quantity]*[Price])". The project got back burnnered for a
while, and it now gives the #Error anytime I use that formula.

Here is what I have tried (and partly the reason I want to pull my hair
out):
1) "=[Quantity]*[Price]" --> gives the calculation for the selected
record
2) "=Sum(1)" --> #Error
3) "=Sum(2)" --> 4 (there are two records, and the result scales up
for all numbers greater than 2)
4) Recreated the textbox (without copying and pasting) and the same
error occurs

So what I don't understand is:
a) why Sum(1) would produce an #Error rather than display "1" ?
b) why Sum([Quantity]*[Price]) used to work, and now it doesn't

As a side note there is also a report I made that used to work also,
that now crashes access anytime I run it. It makes me wonder if the
whole file was corrupted somehow, but the repair option didn't fix
anything.

I am open to any suggestions, causes or solutions.
 
Sean,
In your subform footer, you can't sum a "calculated" unbound control.
In the query behind your subform create a calculated field like this in the query
design grid...
LineTotal : Price * Qty
That creates a "bound" field with the calculated LineTotal.
Place LineTotal field in your sub records instead of your previous calculated field.
Now... in the footer, a calculated text control called TotalPrice with a RecordSource
of...
=Sum(LineTotal)
willl yield the sum all indiviodual LineTotals.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
Back
Top