CALCULATIONS

[Vic]

What did I do wrong.

How to start. I've have a form that a user puts in mileage information.
Then I created a table for different mileage rate.

On the main form I have a field called LdMilRteMT. This is the table
tbl_LoadInfo and is defined as a lookup field to table tbl_MileageRate. This
field is a popup and the bound column is 1 which is the ID in the table
called tbl_MileageRate Rate that holds the different rate. The field showing
is the actual mileage rate that is selected. Let's say $.40 per miles.

Then I went through the help menu and created a text field to actually show
the payout for the mileage. In that text field I put the in the control
source the following:

=[LdMilRteMT]*[LdMtMiles]

This works great except for one problem. It doesn't calculate correctly.
Lets say you have 130 miles * $.40. The total should be $52.00. The total
I'm getting is $130.00.

I found what it's doing. It's taking the record ID or autonum field and
multiplying that times the miles. It's not using the mileage rate displayed
on the screen.

Oh the table that holds the mileage information is called tbl_MileageRate.
The field that actually holds the mileage rate information in that table is
called of course MileageRate.

Any idea's where I went wrong. I really would just prefer to store the
results in another field but from what I've read I guess that's a bad idea.

I received this response

Easy!

Without qualification Access will use the value of your list box or combo
box that you have correctly identified as being the rate id. (You specified
this when you defined the bound column as being 1.)

I assume the actual rate is in column 2 and I further assume it's numeric so
all you have to do tell Access to use the value in the second column - BUT
BEWARE, internally these columns are numbered relative to zero. The
expression you want is therefore:

=[LdMilRteMT].col(1)*[LdMtMiles]

But now this happens

I pasted the following =[LdMilRteMT].col(1)*[LdMtMiles] in the control box
field. As soon as I did that the field changes to #Name?. Also, when I put
that code in, it put the following in the field .[col](1).

I'm not quite sure where to go from here. HELP.

Anyone have any suggestions?

Thanks

 

[Clifford Bass]

Hi Vic,

It should be Column(1) not col(1). If that does not solve the issue,
what is the RowSource for the combo box? If a query, what is the query? If
the table, what are the table's fields? And what is the bound column?

Also, storing the results in your table can be a good idea. Especially
if the mileage rates can change. So if this year your Mileage rate for
mileage category #1 is $0.40 and next year it is $0.42, then next year when
you run a query in this year's data using the $0.42 rate, you will get bad
results. In which case when someone picks the mileage rate, you use an After
Update event on the combo box to calculate the mileage payment. And you
would use an After Update event on the miles text box also so when that is
changed it recalculates the mileage payment. Alternatively, or even in
addition to storing the mileage payment, you could store the rate. Another
option would be to make your mileage categories dependent on the year by
adding a year field. You would also have to be able to get at the year in
the main data. Which you probably already can if you are storing a travel
date for something similar. Then you could avoid the bad calcuations because
they would also look at the year. In fact, this may be the better option
because you would then have a historical record of the mileage rates. It can
get a bit more complicated making sure your combo box provides the correct
year's rates, but it is quite doable. And, if the rates can change at random
intervals, you can use what is called effective dating where you record the
date of the change and the new value. This increases the complexity of the
issue more, but again is quite doable.

Hope that helps,

Clifford Bass

What did I do wrong.

How to start. I've have a form that a user puts in mileage information.
Then I created a table for different mileage rate.

On the main form I have a field called LdMilRteMT. This is the table
tbl_LoadInfo and is defined as a lookup field to table tbl_MileageRate. This
field is a popup and the bound column is 1 which is the ID in the table
called tbl_MileageRate Rate that holds the different rate. The field showing
is the actual mileage rate that is selected. Let's say $.40 per miles.

Then I went through the help menu and created a text field to actually show
the payout for the mileage. In that text field I put the in the control
source the following:

=[LdMilRteMT]*[LdMtMiles]

This works great except for one problem. It doesn't calculate correctly.
Lets say you have 130 miles * $.40. The total should be $52.00. The total
I'm getting is $130.00.

I found what it's doing. It's taking the record ID or autonum field and
multiplying that times the miles. It's not using the mileage rate displayed
on the screen.

Oh the table that holds the mileage information is called tbl_MileageRate.
The field that actually holds the mileage rate information in that table is
called of course MileageRate.

Any idea's where I went wrong. I really would just prefer to store the
results in another field but from what I've read I guess that's a bad idea.

I received this response

Easy!

Without qualification Access will use the value of your list box or combo
box that you have correctly identified as being the rate id. (You specified
this when you defined the bound column as being 1.)

I assume the actual rate is in column 2 and I further assume it's numeric so
all you have to do tell Access to use the value in the second column - BUT
BEWARE, internally these columns are numbered relative to zero. The
expression you want is therefore:

=[LdMilRteMT].col(1)*[LdMtMiles]

But now this happens

I pasted the following =[LdMilRteMT].col(1)*[LdMtMiles] in the control box
field. As soon as I did that the field changes to #Name?. Also, when I put
that code in, it put the following in the field .[col](1).

I'm not quite sure where to go from here. HELP.

Anyone have any suggestions?

Thanks