How to ensure accurate mileage expense calculations??

[tlyczko]

Hello,

I have read about currency calculations, etc. in this newsgroup, and I
understand that currency math will be sufficiently accurate for what I
need to do for a mileage/expense report.

Where I'm stuck is what data type etc. I should use for the mileage
distance number -- single?? decimal?? for the amount of mileage
distance traveled, to two decimal places maximum, but allowing for no
decimals or one decimal place or two decimal places.

Then in a query or even form field which calculates the mileage
reimbursement money amount, I need to multiply the mileage amount times
$.31 to get the money amount, and have the query result be an accurate
currency value displayed to 2 decimal places, therefore I need to have
an accurate data entry for the mileage distance amount (e.g. 10.0 miles
or more).

Basically the expense report should work more or less like this
unfinished HTML example page:

http://www.visualwave.com/expense.

This simple page auto-calculates expenses or mileages in each row and
adds them up in each column, and generates a grand total, but contains
various rather unwieldy JavaScript, etc. It's been tested for the
calculations, but the form data entry validations are not so hot (for
proper decimal places). The first row works best. Perhaps I should
stick with this, and backend a database to this form...

My other option is to use Visual Web Developer and ASP.NET 2.0 against
an Access database, but that also requires validation, queries, etc.
that Access can easily do for me, even if I do have to give 150 people
an MDE file, sigh.

Thank you for reading this,
Tom

[Allen Browne]

I think you want your "mileage" field to contain the number of miles
travelled (not the odometer reading of the vehicle)? If so, your table will
have fields like this:
MileageID AutoNumber primary key
MileageDate Date/Time When this expense occurred.
MileageDescrip Text (255)
Miles Number (Long, or Double if you need fractions)
Rate Currency $/mile

You will then create a query to calculate the expense for the row. Type this
into a fresh column in the Field row:
Expense: [Miles] * [Rate]

If you used a Double as the size for the Miles field, then round to the
nearest cent like this:
Expense: Round([Miles] * [Rate])

To enure that is interpreted correctly, use:
Expense: CCurNz(Round([Miles] * [Rate]),0))

You can now create a report based on that query that sums the total.

If the idea of doing calculated fields in a query (instead of storing in a
table) is new, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tlyczko" <tl*****@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I have read about currency calculations, etc. in this newsgroup, and I
understand that currency math will be sufficiently accurate for what I
need to do for a mileage/expense report.

Where I'm stuck is what data type etc. I should use for the mileage
distance number -- single?? decimal?? for the amount of mileage
distance traveled, to two decimal places maximum, but allowing for no
decimals or one decimal place or two decimal places.

Then in a query or even form field which calculates the mileage
reimbursement money amount, I need to multiply the mileage amount times
$.31 to get the money amount, and have the query result be an accurate
currency value displayed to 2 decimal places, therefore I need to have
an accurate data entry for the mileage distance amount (e.g. 10.0 miles
or more).

Basically the expense report should work more or less like this
unfinished HTML example page:

http://www.visualwave.com/expense.

This simple page auto-calculates expenses or mileages in each row and
adds them up in each column, and generates a grand total, but contains
various rather unwieldy JavaScript, etc. It's been tested for the
calculations, but the form data entry validations are not so hot (for
proper decimal places). The first row works best. Perhaps I should
stick with this, and backend a database to this form...

My other option is to use Visual Web Developer and ASP.NET 2.0 against
an Access database, but that also requires validation, queries, etc.
that Access can easily do for me, even if I do have to give 150 people
an MDE file, sigh.

Thank you for reading this,
Tom

[tlyczko]

Hello Allen,

Thank you for responding, this is what I'm doing, I appreciate your
adding the comments about rounding, since there are so many comments
about this topic, I wasn't sure if setting the number of decimal places
etc. was the way to round a Double.

I'll also read the newsgroup about Round().

However, if I want the Miles number to be a decimal (e.g. 29.6 or 84.33
miles) you suggested I use Double.

Will this be sufficiently accurate that when I round it, multiply it by
the Rate, then cast it to Currency, that I will get an accurate dollar
amount??

I'll do my own testing, but after having read some of the impassioned
posts about rounding, currency, etc. I just wanted to be sure.

Thank you, Tom

[Allen Browne]

You can use the Currency data type to store things other than money. You
might like to use a Currency field, and set the Format to Fixed and the
Decimal Places to 2.

Than use Round() when you make the calculations in the query, and the result
will be accurate. Again, you may wish to set the Format of the field in the
query (or the text box on the form/report) so it is Fixed, 2-decimal place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tlyczko" <tl*****@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hello Allen,

Thank you for responding, this is what I'm doing, I appreciate your
adding the comments about rounding, since there are so many comments
about this topic, I wasn't sure if setting the number of decimal places
etc. was the way to round a Double.

I'll also read the newsgroup about Round().

However, if I want the Miles number to be a decimal (e.g. 29.6 or 84.33
miles) you suggested I use Double.

Will this be sufficiently accurate that when I round it, multiply it by
the Rate, then cast it to Currency, that I will get an accurate dollar
amount??

I'll do my own testing, but after having read some of the impassioned
posts about rounding, currency, etc. I just wanted to be sure.

Thank you, Tom

[tlyczko]

Hello Allen,

Thank you for the advice about the currency datatype.

I can see I'll have to run examples etc., since I hope to eventually
use an ASP.NET 2.0 form and HTML table output for this after getting
the Access query set up right.

Thank you, Tom