Expressions

[Tom]

The Expression of [EX AM1]/[ON BILL] gives me a percentage and this
Expression [amt pd]-[ex am1]-[ex am2]-[ex am3]-[ex am4] gives me a sum
minus the columns named.

However, I want to add most of the columns named in the second Expression
using [EX AM2]+[EX AM3]+[EX AM4] and it only extends out the numbers like EX
AM2 =0018000 and EX AM3=0026500 to 00180000026500 etc. I need it to add
18000+26500 to = 44500.

How can I get it to add the three colums and show a total?

Any insight or help is appreciated.

 

[golfinray]

Tom
Are you trying to do this on a form or in a query? On a form, put a textbox
and make the control source:
=Sum([amt pd]-[ex am1] etc and remember to encase it all in parenthesis.
In a query you would need a sum function, the little E on your tool bar,
with that data all iincluded.

 

[Tom]

Andrew

Thanks. It worked. Another question: How do you get the end result of this
to be dollars?

I worked 3 hours on trying to get the below to work and your time is
appreciated.
--
Tom

It would appear that the various fields are strings.

In this case you would need to convert the strings to values in order to
then add them together. This would be acheived using the Val function.

e.g. Val("0018000") would give 18000

You would need to convert each field to a value and add them together;
Val([EX AM2])+Val([EX AM3])+Val([EX AM4])

If any of the fields contain a Null value then this will fall over with an
invalid use of Null error.

In this case you could further alter your code as follows;
Val([EX AM2] & "")+Val([EX AM3] & "")+Val([EX AM4] & "")

Hope this helps.

The Expression of [EX AM1]/[ON BILL] gives me a percentage and this
Expression [amt pd]-[ex am1]-[ex am2]-[ex am3]-[ex am4] gives me a sum
minus the columns named.

However, I want to add most of the columns named in the second Expression
using [EX AM2]+[EX AM3]+[EX AM4] and it only extends out the numbers like EX
AM2 =0018000 and EX AM3=0026500 to 00180000026500 etc. I need it to add
18000+26500 to = 44500.

How can I get it to add the three colums and show a total?

Any insight or help is appreciated.

 

[Andrew Tapp]

I'm assuming you want it formatted as dollars, not converted to dollars?

In order to format the output you would use the Format function;
Format(Val([EX AM2] & "")+Val([EX AM3] & "")+Val([EX AM4] & ""),"$#,##0.00")

Alternatively if it's a field in say a form or report then you could change
the fields format property to $#,##0.00

And if it's a field in a query, from the query design, right click on the
field and change the format property to $#,##0.00

Hope this helps.

Andrew

Thanks. It worked. Another question: How do you get the end result of this
to be dollars?

I worked 3 hours on trying to get the below to work and your time is
appreciated.
--
Tom

It would appear that the various fields are strings.

In this case you would need to convert the strings to values in order to
then add them together. This would be acheived using the Val function.

e.g. Val("0018000") would give 18000

You would need to convert each field to a value and add them together;
Val([EX AM2])+Val([EX AM3])+Val([EX AM4])

If any of the fields contain a Null value then this will fall over with an
invalid use of Null error.

In this case you could further alter your code as follows;
Val([EX AM2] & "")+Val([EX AM3] & "")+Val([EX AM4] & "")

Hope this helps.

The Expression of [EX AM1]/[ON BILL] gives me a percentage and this
Expression [amt pd]-[ex am1]-[ex am2]-[ex am3]-[ex am4] gives me a sum
minus the columns named.

However, I want to add most of the columns named in the second Expression
using [EX AM2]+[EX AM3]+[EX AM4] and it only extends out the numbers like EX
AM2 =0018000 and EX AM3=0026500 to 00180000026500 etc. I need it to add
18000+26500 to = 44500.

How can I get it to add the three colums and show a total?

Any insight or help is appreciated.