Not Able to Total Field as Numbers

[majoh60]

I am very new to Access. I am designing a database to help with government report that we must complete quarterly. I made a query and then designed a report based on that query. When we report to the government, they want the amounts for each transaction reported like this


(50,000.00 must be reported as 50). I have attached a screenshot.

In the query field I used this format FormatAmt: Format([Amt],"0,") however when I try to total this field on the report, the only options I have are to "count records" or "count values". What I need is for that column to total as numbers because there is an additional reporting requirement when a certain threshhold is reached. Sorry if this sounds stupid but I am realyy a nebie and this is my first stab at this.

[JoeM]

The Format function returns a Text value, not numerical. Get rid of it. If you want to change how the numbers look on the report, do it in the Text Box that returns the value in the Report itself. That way, you leave the numbers in numerical format and can still perform mathematical computations on them.

[rpeare]

are you rounding or just reporting the thousands column?

If you're rounding you just have to divide the number by 1000 and round to 0 decimal places.
[YourValue]/1000 in a query or report

i.e. 50,501 would become 51
if you want to report the same number as 50.50 just change the rounding to 2 places.

if you want to round any portion short of 51,000 as 50 you'd do something like

int([YourValue]/1000)

[majoh60]

I am very new to Access 2007 and I posted a message yesterday to get help creating a calculated field that would take a number such as 1500 and round it to 2. I got great responses and was able to do it. My problem now is with one amount. This information is for a government report so the data has to be very accurate. All of the numbers round correctly except 500.00. 500.00 needs to round up to 1 but the querry formula rounds it down to 0. This is the only number that is behaving this way which is strnge in itself. Is there a way to update the calculated field so that $500.00 will automaticaaly round to 1 instead of 0? I hope this makes sense. I am attaching a screen print of my query results for review.

[June7]

Note that I merged your two threads as this is really a continuation of same topic.


This is because Round() in Access follows an even/odd rule and apparently 0 gets the even treatment. Example:

Round(4.4,0) = 4
Round(4.5,0) = 4
Round(4.6,0) = 5
Round(3.5,0) = 4
Round(0.5,0) = 0
Round(2.50085,0) = 3
Round(2.5) = 2

However, Format() does not follow even/odd rule. Example:

Format(4.4,"0") = 4
Format(4.5,"0") = 5
Format(4.6,"0") = 5
Format(3.5,"0") = 4
Format(0.5,"0") = 1

Convert the string that results with Format back to a number with Val()

Val(Format(4.5,"0"))

[majoh60]

Thank you, Thank you, Thank you. It worked like a charm.