Calculation result in text box

[Charlie]

I am trying to perform a calculation in a text box on a sub form that adds the amounts, i tried using this code =sum([amount]) but this gives me #error as a result, can someone point out my mistake so I can do it for myself please?

The file is here:
http://homepage.ntlworld.com/charlie_chuck/invoice12.mdb


Charlie

 

[Davepacey]

First, change the format of your subform to continuos forms, then in the form footer, create a textbox called "Total", set its properties to =Sum([Amount]), set properties visible to no, save subform.You will also need to get all controls on one line for your subform, as in the file I have returned to you Charlie.
on your main form, set the properties of your textbox to =[tblinvoice Subform]!Total

This will work.

Dave
Note:
all the names above are as supplied in the original file
This message was edited by Davepacey on 2003-02-17 17:00

 

[Charlie]

Thanks I have finally managed it, using another method.

Charlie

 

[Davepacey]

ok, good

 

[Charlie]

This is how:
I opened the form SupplierInvoicesSubform in design view.
Then I added a text box to the footer section and named it txtInvoiceTotal.
I changed the label test to Invoice Total.
In the textbox contro; property I entered =sum([Amount]).
I then tested it by going to formview on the view menu.
I then saved and closed the form.

I then opened the main form frmSupplierInvoices in design view.
then added a textbox under the subform.
I changed the label to Invoice total.
Then I selected the control source property and hit the expression builder.
In the exp builder I selected forms,loaded forms,frmSupplierInvoices,frmSupplierInvoicesSubform in the first column and txtInvoiceTotal in the second column and hit paste. i then finished the formatting etc.
Phew...It only took me ALL day though!

Charlie

 

[Davepacey]

Exactly the same method as in my reply !

 

[Charlie]

Was it? sorry, im not familiar with this program.

 

[PTBA]

Formatting values in an expression

I have created a query to extract certain data for download to excel. As I don't want null or zero values to be converted I have built an IIF expression selecting only values greater than zero. IIf(([Wage]![entry]>0),([Wage]![entry]),"").
While the table values are formatted as currency the query yields values that have infinite decimals and that are not read by excel as a number even tho I have set the property in the query expression to currency. Any suggestions as to what I may have done wrong.
Thanks
Peter

 

[bat17]

I suspect that by forcing Access to use an empty string rather than a Null value you are turning it into a text field.

Peter

 

[PTBA]

Thanks is there any way I can have zeros and null fields import to excel as a blank field?