CALCULATION

[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.

Thanks Everyone

 

[Rod Plastow]

Hi Vic,

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]

Rod

 

[Vic]

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.

Thx

Hi Vic,

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]

Rod

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.

Thanks Everyone

 

[Rod Plastow]

Sorry Vic,

I was assuming VBA code but you are using the Property Sheet - I know of no
way of addressing the columns from the Property Sheet (anyone?).

I also assume you don't want to get involved in VBA code at this juncture so
is there a work-around? I further assume you don't allow your users to edit
the mileage rates from this form so you can limit-to-list the mileage rate
box. Simply change the bound column to 2 (or whatever) and use your original
expression for calculating the extended cost.

I tested this and think there's a bug (or at least a quirk). If you set the
column width of column 1 to zero everythings works well. However if you set
the column width of column 1 to a non zero value the list displays correctly
with two columns but the control itself is blank. The calculation however is
correct. This was using A2007 and a Combo Box.

 

[John W. Vinson]

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 think Rod was misremembering the syntax - it should be Column(1) not col(1).

 

[Rod Plastow]

I think John is absolutely right - and it does work from the Property
Sheet. In my defense: too much time spent in the VBA coding environment with
Intellisense to remember it's Column and not Col.

Thanks John.