Form Problem in Footer Section

[Steve101]

I am attempting to put an Order Total in the footer section of a Form in an ADP project. In the detail section of the form I have two controls bound to a table in the backend (SQL Server 2000) database. These fields are Quantity and UnitPrice (Float and a SmallMoney field respectively), with a third control called LineTotal having a controlsource of "=Quantity * UnitPrice"

In the Footer section, I have tried a control, set to:

=Sum(LineTotal)
or
=Sum(Quantity * UnitPrice)

but neither of these work. They produce a #error in the derived field.

This method works fine in a straight MDB environment, so I'm a little baffled why its not working here.

Has anyone experienced this problem before, or recognised a flaw in what Im doing; is there a simple workaround; I'm reluctant to code around it (using events) unless I have to.

Thanks in advance,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[yaya013]

Hi

Try =Sum(Quantity) * Sum(UnitPrice)

and if it will not help

the simple workaround is to calc the value by loop

 

[neecie]

Try:

 = Sum([Quantity]* [UnitPrice])

Or

 = Sum([LineTotal])

 

[Steve101]

Thanks both; have tried all of those variations before, but no joy; as I said, works fine in the mdb environment; yaya013, Sum(Quantity) * Sum(UnitPrice) may work, but of course does not give the right answer.

Thanks anyway,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[cjowsey]

One solution is to let SQL Server do the work and write a small user defined function. You will need to use a view rather than a table as the data source for your form. Just put the call to the function in the view and you don't have to worry about doing the arithmetic in the front end.

Regards,
Clive

 

[Steve101]

Thanks Clive; that's a good idea. I've actually already coded it in the frontend. Not much effort required to do this. I made the decision to do it this way, as the data is already sitting on the client (in the subform datasheet), so it did'nt seem necessary to do another round trip to the client.

Cheers,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)