FIELD CALCULATION

[Vic]

I have a form that has three fields.

Hubstart, Hubend and total mileage.

I want to store the total in the total mileage field by subtracting hubstart
from hubend.

I've tried a number of things but I just can't seem to figure out how to
write it correctly when I select on exit as an expression in hubend.

Can someone please help me out with this.

Thx

 

[Allen Browne]

Don't store the total in your table.

Instead, create a query, and type this into the Field row in query design:
Total: [HubEnd] - [HubStart]
Save the query, and use it anywhere you would have used your table. The
beauty is that this can never go wrong the way it could if you stored it in
the table.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Vic]

Thanks for your response but I think I failed to communicate some
information. Not quite sure how to do this without getting real lengthy.

The form I'm using is setup on a tab. This form pulls a number of different
records that have been entered for 1 customer i.e. could have 5 different
customer for let's say one load number. So basically you can have one load
number but 5 different customer.

Everything works wells exactly the way I would like it too with my limited
knowledge. However, I want to be able to track the actual miles from one
customer to another. The probelm is if I put ([hubend])-([hubstart]) the
figures shows up for all customer and then it just keeps adding rather then
maintain a unique hub start and end figure for each customer with total miles.

I hope I didn't make this really harder then I needed to. Kind of hard to
communicate what I see to writing. Any idea's on what I can do.

Thx

Don't store the total in your table.

Instead, create a query, and type this into the Field row in query design:
Total: [HubEnd] - [HubStart]
Save the query, and use it anywhere you would have used your table. The
beauty is that this can never go wrong the way it could if you stored it in
the table.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that has three fields.

Hubstart, Hubend and total mileage.

I want to store the total in the total mileage field by subtracting
hubstart
from hubend.

I've tried a number of things but I just can't seem to figure out how to
write it correctly when I select on exit as an expression in hubend.

Can someone please help me out with this.

Thx

 

[Allen Browne]

Whatever the situation, the solution will be to build normalized tables.
That means you do not store the calculated result.

If you have one load number that represents the entire distance, then the
HubStart and HubEnd belong in the Load table, not in the detail table. If
you storing each leg of the journey, then these fields belong in the Detail
table, and the sum will give the correct results.

If you want to ignore the rules and build a non-normlalized table and take
on the responsibility for ensuring that no bad data ever gets into your
table instead of knowing that Access will calculate it correctly for you,
the link I posted explains how you can do it by using the AfterUpate event
procedure of the 2 text boxes to store the calculated result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response but I think I failed to communicate some
information. Not quite sure how to do this without getting real lengthy.

The form I'm using is setup on a tab. This form pulls a number of
different
records that have been entered for 1 customer i.e. could have 5 different
customer for let's say one load number. So basically you can have one
load
number but 5 different customer.

Everything works wells exactly the way I would like it too with my limited
knowledge. However, I want to be able to track the actual miles from one
customer to another. The probelm is if I put ([hubend])-([hubstart]) the
figures shows up for all customer and then it just keeps adding rather
then
maintain a unique hub start and end figure for each customer with total
miles.

I hope I didn't make this really harder then I needed to. Kind of hard to
communicate what I see to writing. Any idea's on what I can do.

Thx

Don't store the total in your table.

Instead, create a query, and type this into the Field row in query
design:
Total: [HubEnd] - [HubStart]
Save the query, and use it anywhere you would have used your table. The
beauty is that this can never go wrong the way it could if you stored it
in
the table.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

I have a form that has three fields.

Hubstart, Hubend and total mileage.

I want to store the total in the total mileage field by subtracting
hubstart
from hubend.

I've tried a number of things but I just can't seem to figure out how
to
write it correctly when I select on exit as an expression in hubend.

 

[Vic]

Thanks. That's part of the problem though. I don't understand all that is
required. It is data that is actually part of the detail i.e. track is leg
of the trip as you say. I just don't have the experience necessary to be
able to do that.

Thanks for your time.

Whatever the situation, the solution will be to build normalized tables.
That means you do not store the calculated result.

If you have one load number that represents the entire distance, then the
HubStart and HubEnd belong in the Load table, not in the detail table. If
you storing each leg of the journey, then these fields belong in the Detail
table, and the sum will give the correct results.

If you want to ignore the rules and build a non-normlalized table and take
on the responsibility for ensuring that no bad data ever gets into your
table instead of knowing that Access will calculate it correctly for you,
the link I posted explains how you can do it by using the AfterUpate event
procedure of the 2 text boxes to store the calculated result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response but I think I failed to communicate some
information. Not quite sure how to do this without getting real lengthy.

The form I'm using is setup on a tab. This form pulls a number of
different
records that have been entered for 1 customer i.e. could have 5 different
customer for let's say one load number. So basically you can have one
load
number but 5 different customer.

Everything works wells exactly the way I would like it too with my limited
knowledge. However, I want to be able to track the actual miles from one
customer to another. The probelm is if I put ([hubend])-([hubstart]) the
figures shows up for all customer and then it just keeps adding rather
then
maintain a unique hub start and end figure for each customer with total
miles.

I hope I didn't make this really harder then I needed to. Kind of hard to
communicate what I see to writing. Any idea's on what I can do.

Thx

Don't store the total in your table.

Instead, create a query, and type this into the Field row in query
design:
Total: [HubEnd] - [HubStart]
Save the query, and use it anywhere you would have used your table. The
beauty is that this can never go wrong the way it could if you stored it
in
the table.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

I have a form that has three fields.

Hubstart, Hubend and total mileage.

I want to store the total in the total mileage field by subtracting
hubstart
from hubend.

I've tried a number of things but I just can't seem to figure out how
to
write it correctly when I select on exit as an expression in hubend.

 

[Allen Browne]

You need 2 tables:
tblLoad, with fields something like this:
- LoadID AutoNumber
- DriverID relates to the primary key of your table of drivers
- LoadDate date when you delivered this load (assuming short haul)

tblLoadLeg, with fields like this:
- LoadLegID AutoNumber primary key
- LocStart where this leg started from.
- LocEnd where this leg ended.
- HubStart Number odometer reading at start
- HubEnd Number odometer reading at end

Create a query based on tblLoadLeg.
Type the expression into the Field row in query design:
LegDistance: [LocEnd] - [LocStart]
Save.

Create a main form bound to tblLoad.
Create a subform based on the query.

Set the subform's Default View property to Continuous Form.
Cut the labels from the text boxes and put them in the Form Header.
(If you don't see Form Header/Footer, it's on the View menu.)
Arrange the text boxes are side-by-side.
Drag the height of the Detail section up (one text box high.
In the Form Footer section, add a text box with Control Source of:
=Sum([LegDistance])

There's probably much more than this to it, and there are better ways of
handling the mileage for each leg (so the database automatically uses the
last HubEnd and LocEnd without having to enter them again), but that should
get you started.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. That's part of the problem though. I don't understand all that
is
required. It is data that is actually part of the detail i.e. track is
leg
of the trip as you say. I just don't have the experience necessary to be
able to do that.

Thanks for your time.

Whatever the situation, the solution will be to build normalized tables.
That means you do not store the calculated result.

If you have one load number that represents the entire distance, then the
HubStart and HubEnd belong in the Load table, not in the detail table. If
you storing each leg of the journey, then these fields belong in the
Detail
table, and the sum will give the correct results.

If you want to ignore the rules and build a non-normlalized table and
take
on the responsibility for ensuring that no bad data ever gets into your
table instead of knowing that Access will calculate it correctly for you,
the link I posted explains how you can do it by using the AfterUpate
event
procedure of the 2 text boxes to store the calculated result.

Thanks for your response but I think I failed to communicate some
information. Not quite sure how to do this without getting real
lengthy.

The form I'm using is setup on a tab. This form pulls a number of
different
records that have been entered for 1 customer i.e. could have 5
different
customer for let's say one load number. So basically you can have one
load
number but 5 different customer.

Everything works wells exactly the way I would like it too with my
limited
knowledge. However, I want to be able to track the actual miles from
one
customer to another. The probelm is if I put ([hubend])-([hubstart])
the
figures shows up for all customer and then it just keeps adding rather
then
maintain a unique hub start and end figure for each customer with total
miles.

I hope I didn't make this really harder then I needed to. Kind of hard
to
communicate what I see to writing. Any idea's on what I can do.

Thx

:

Don't store the total in your table.

Instead, create a query, and type this into the Field row in query
design:
Total: [HubEnd] - [HubStart]
Save the query, and use it anywhere you would have used your table.
The
beauty is that this can never go wrong the way it could if you stored
it
in
the table.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

I have a form that has three fields.

Hubstart, Hubend and total mileage.

I want to store the total in the total mileage field by subtracting
hubstart
from hubend.

I've tried a number of things but I just can't seem to figure out
how
to
write it correctly when I select on exit as an expression in hubend.