MILEAGE PAY CALCULATION

[Vic]

I'm a truck driver. Based on where I'm heading I get paid different rate for
empty miles and loaded miles.

I developed a database to track all my load information but I'm having a
problem with a certain calculation which I can't figure out.

I have one table that lists the Carrier or let say company information. I
have another table that is liked to the Carrier table that lists the various
mileage pay rate. All that works fine.

I have a table that tracks all my load information. In this table I have a
field called LdMilRteMt. This field is defined as a combo box which lists
all the various rates for each carrier. This part works fine as well. Also,
in this table I have two other fields that need to work with each other
called LdMtMiles. This field number of miles is entered by myself. The
other field is LdPdMt. I will explain this one further.

Now the form. I have the field LdMilRteMt as a combo. The information
displayed in this field is the mileage rate for the carrier: Here is the
info. ELECT tbl_MileageRate.MileageAutoNumb, tbl_MileageRate.MileageRate,
tbl_MileageRate.MileageType, tbl_MileageRate.MileageDesc,
tbl_Carrier.CarFullName, tbl_Carrier.CarPhone FROM tbl_Carrier INNER JOIN
tbl_MileageRate ON tbl_Carrier.CarAutoNum = tbl_MileageRate.MileageCarrier
ORDER BY tbl_MileageRate.MileageRate, tbl_Carrier.CarFullName;

I'm trying to get the mileage rate being displayed in LdMilRteMt * LdMtMiles
and the have that revenue figure appear in LdPdMt without me have to do this
myself using a calculator.

I've tried previously doing this using a text field but that doesn't store
the information and I really want to do that because of the constant changes.
Also, when I did do it that way LdMilRte would multiply the carrier Id
(let's say it's carrier 1) that the miles (let's say 138). For an example
the pay for 138 * .40 a miles is $55.20. It would give me a figure of
$138.00 instead.

I've tried a number of recommendation with that problem but could not get it
to work correctly.

Can anyone please guide me through this. I'm not very knowledgeable on the
programming side so how can I get the display on one filed to be multiplied
by the information entered in another field and automatically give me a
dollar value in a third field.

Thanks

 

[Ken Snell \(MVP\)]

See this article to see how you can use a value from a combo box's column
OTHER THAN the bound column in order to put a value in a textbox on the
form. The second example shows how to do this when the textbox is bound to a
field in the form's RecordSource query, which appears to be the situation
you have here.

http://www.mvps.org/access/forms/frm0058.htm

 

[tina]

This field is defined as a combo box which lists
all the various rates for each carrier.

and as a side note to Ken's response, if you are saying above that you have
a Lookup field *in your table* , recommend you get rid of it immediately.
combobox controls in *forms* are fine, no problem there. for more
information, see http://home.att.net/~california.db/tips.html#aTip8

hth

 

[Vic]

The field that I want the result to show in is LdPdMt is NOT a text field.
This is a valid field in the table tbl_LoadInfo. Also the field that is a
combo box is called LdMilRteMt. The bound column is this field naturally is
the carrier. The display info is the mileage rate. I want to be able to
multiply the mileage rate (what shows on the screen) from the combo selection
in LdMilRteMt.

Let me try this again. I want to try and multiply LdMtMiles * the rate
showing in LdMilRteMt and store that value in LdPdMt.

LdPdMt is a currency field.
LdMilRteMt column 2 is a currency field. However, column 1 is the bound
column and this is the ID number for the carrier mileage rate.
LdMtMiles is a number field where the number of miles is entered.

Is there a way to do this? I don't know what other way to explain it. None
of the fields are text fields. I looked at the response and that article
doesn't apply here. I had tried a text field before and it didn't work out
either. Someone recommended I store the results which made a lot of sense to
me so that is the way it is setup now.

I am now manually calculating these figures myself. There has to be a
better and simplier way to do this. I'm not programming savyy so a lot of
these things I don't understand. Can someone please help.

 

[Ken Snell \(MVP\)]

Yes, it's easily done. You would use the AfterUpdate event of the combo box
to run this code:

' ***** Code Start *****
Private Sub LdMilRteMt_AfterUpdate()
Me.LdPdMt.Value = CCur(Me.LdMilRteMt.Column(1)) * _
Me.LdMtMiles.Value
End Sub
' ***** Code End *****

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/