Format and Calculate on same field in Query

  • Thread starter Thread starter Westley via AccessMonster.com
  • Start date Start date
W

Westley via AccessMonster.com

I have a query with field named: amtpaid
I calculate this field to be amtpaid:[invamt]-[otheramt]
It gives me the calculated amount in this field, but the number is showing
$sign

How do i remove the $sign from this calculated field?

I know how to format the field with Expr2:Format([amtpaid],"000.00") But not
sure how do do both format and calculate in the same field.

I also, tried to split the field to format in one field and calculate in the
other, but the problem with that is that i need only the one field to show up
in my query.

Please help....

Thanks,
Westley
 
I have a query with field named: amtpaid
I calculate this field to be amtpaid:[invamt]-[otheramt]
It gives me the calculated amount in this field, but the number is showing
$sign

How do i remove the $sign from this calculated field?

I know how to format the field with Expr2:Format([amtpaid],"000.00") But not
sure how do do both format and calculate in the same field.

I also, tried to split the field to format in one field and calculate in the
other, but the problem with that is that i need only the one field to show up
in my query.

Please help....

Thanks,
Westley
Click to expand...

If you always want the format to be 000.00, then
amtpaid:Format([invamt]-[otheramt],"000.00")

Otherwise use:
amtpaid:Format([invamt]-[otheramt],"#0.00")
 
Westley said:
I have a query with field named: amtpaid
I calculate this field to be amtpaid:[invamt]-[otheramt]
It gives me the calculated amount in this field, but the number is showing
$sign

How do i remove the $sign from this calculated field?

I know how to format the field with Expr2:Format([amtpaid],"000.00") But not
sure how do do both format and calculate in the same field.

I also, tried to split the field to format in one field and calculate in the
other, but the problem with that is that i need only the one field to show up
in my query.
Click to expand...


A possibly better way is to keep the calculation and format
separate. Use the Format property of the text box on your
form/report to specify the format and leave the calculation
in the record source query (or put it in the text box's
ControlSource). You only need to combine the two when you
are formatting the value as part of a bigger expression such
as:
"Amount Due: " & Format(amtpaid, "0.00")
but that's not a normal thing to do in a query.
 
Back
Top