Incorrect Totals (1 Viewer)

[BernieAnne]

Some, but not all, of my report totals are incorrect. They are only out by one, however it is annoying just the same and need to be correct. I know what the problem is but not how to correct it.

My table and form are set up to divide gallons by .26420 to convert to litres. In "properties" I have set up the new number to be a Fixed number with "Zero" decimal places. The number prints out fine on my report, however, when it sums up the column it is adding up the original number with decimal places, rather than the fixed number with zero decimal places. (I hope that makes sense!!) Most of the time the total comes out correct, however, on the odd occassion the total is out by 1. Does anyone know how I correct this?

Thanks for any help you can offer.

 

[bjackson]

What format do you store your litres in your table

if you dont need to store the numbers with decimal points
then when you do your conversion calculation round the
number before it is stored in the table

eg litres=round((gallons/.26420),0)

 

[BernieAnne]

BJackson

Thanks for you reply. I need a little more help. You'll have to forgive me, I am new to Access after having used a DOS database these past nine years so all of this is very foreign to me.

I went into my query and in the two columns I went to "build" and put in the following:

US Gallons: =round(([Litres]*.26420),0)
Cdn Litres: =round(([Gallons/.26420),0)

When I go to print my report I get the following error message:
"Undefined function 'round' in expression"

What am I doing wrong? Thanks very much for any help you can give me.

 

[bjackson]

what access version are you using

i have 2000 and when i use your expression it works fine

 

[BernieAnne]

I am using Access '97.

 

[bjackson]

I dont use 97 so maybe 97 hasnt got
the built in function "Round"
I am sorry i cant help you with this but iam sure
someone using these forums will tell you
how to create a custom function


i also notice gallons is missing a "]" closing bracket


Regards Bjackson

 

[BernieAnne]

I missed out the ] bracket. I had that on my query. Just one more question for you. Could this have anything to do with how I have my numbers set up? I set them as "Fixed" with "Zero" decimals. Do I need to adust this at all if I use =round?

 

[bjackson]

can you explain what you mean by Fixed

i understand that in the text boxes in your report
your format would be general number and in your table
you would store your numbers as long integer or single
or double etc

 

[BernieAnne]

When you go into properties you can set the type of number you want; General, Currency, Fixed, etc.

Thanks for all your help.

 

[BernieAnne]

Upgraded to Access XP

Hi BJackson - I just upgraded to Office XP and went back to my query and once again typed in the =round and this time it works so obviously Access 97 didn't have this feature. Thanks again for your help. I shall now continue trying to figure this programme out. This time Microsoft included a tutorial which it didn't with its 97 version. I now understand a little better how to do queries and the difference between the different types of queries.

Take care.