Multiply by -1 problem

[thatlem]

I have a query which sums a yes/no selection box resulting in a negative sum. I then pull this value into a report.

In the past I have been able to multiple the Sumof Field by -1 to correct the value to a positive integer within the field properties in the report. However, my mental block has been preventing me from getting this correct, so I need some insight. Running Office 2007.

I have tried:
=(-(Sumof Field))
=(-[Sumof Field])
=-Sumof Field
=Sumof Field *-1
=([query]![Sumof Field])*-1

Any insight would be appreciated. This should not be this hard.

 

[pbaldy]

I'd use Abs(). If the query is the source of the report, it should simply be:

=Abs([Sumof Field])

Be careful you don't have a textbox with the same name as the field, as that can confuse Access when you try to use the value in a calculation.

 

[thatlem]

You'd think that would work, but I get an error.

The report lets me run it (ie. no error prior to running the report - as if the code was wrong) - error shows up on the report. Expanded the field, set to fixed with 0 decimals - but continue to get error.

 

[pbaldy]

Is the query the source of the report? Did you check for a textbox with the same name? Can you post the db here?

 

[thatlem]

The query is the source of the report. There are no other textboxes with the same name. Very simple straight forward report. Just pulling in fields from a query.

Unfortunately, this is a medical application - so confidential information would be available, so I cannot post. However, will post a couple of screen shots.

Thanks again.

 

[pbaldy]

There are no other textboxes with the same name.

I would start by changing the textbox with the same name. See attached.

 

[thatlem]

Sorry, but that also did not work.

I changed the name of the field to field 101. Then defined =Abs([SumOf Field]) - but when I ran the report, I saw the database flash and convert the field back to SumOf Field before running the report.

So then I went to the builder and defined as =Abs([query]![field]) pulling the same data field as defined above. The database held the calc, but the report continues to come back with an error.

I then added an unbound text field and built the same calc above, but the report did not recognize the query, even though linked at the report level. Then I removed the Abs function and just put a - in front of the [query]![field] and still came back with an error.

 

[thatlem]

I finally got it.

yes, I did need to change the field name, but the calc that worked was:

=(-([SumOf Field]))

Thanks for your assistance.