Creating A button in Microsoft Access 2k3

J

JoshuaGHarvey

an Access database that i wish to show mileage in this way:

I created a data base that has three fields "To town", "From Town", &
"Mileage".
I created a querie that has removed duplicate entries for the "To Town"
& "From town" Fields, also created a "mileage" field querie.
In my form I created three list boxes 1st using the querie for the "To
Town" Field 2nd listing the "From Town" field, and the 3rd using the
"Mileage" field.
I am having a hard time writing the expression for the button that will
let me compare the "to Town" & the "From Town" fields and then show the
mileage in the last list box.

An Help you can give on this would be awesome!!

Thanks
 
D

DL

Are these the only fields in your db?
If so thats bad design
1) The db should be designed so that you cannot have duplicates, unless you
need them
2) The calculation should be done on the fly and not held in a field in the
db
Perhaps explain more fully what "something" and To/From Town actually is.
ie I assume you are measuring 'A Location' to 'To Town', then where is 'From
Town' in all this, a differing Location?
 
J

JoshuaGHarvey

it is designed so you can not have duplicates right now. And yes those
are the only three fields.
its very basic. A "To town" Field.
A "From Town" Field.
A "Mileage" field.
So say in the list Box I select a town Bangor for the "From town" and
Augusta for the "To town" after pushing the button i wish to display
the mileage 76 Miles.
the Fields are set up so that i have several combinations of "tos" &
"froms" dont have a choice really.
So i have the database set up this way
From town To town Mileage
Click to expand...
Bangor Augusta 76
Bangor Bath 114
Calais Caribou 146
ETC...

I am not sure of another way to get the calculation i need with out
having the mileage listed!
 
B

Bob I

The way you described this database you will select record by criteria
where "To town" = selection AND "From town = selection and display all 3
fields. No criteria will be needed for the mileage field, merely show it.
 
D

DL

Then you need a 'Mileage' Tbl with
IDCode (You can use auto number, though perfectionists doent reccomend
this) Primary key
TownFrom text
TownTo text
Mileage number
TownFrom + TownTo indexed to prevent duplicates
All variations of journeys would need to be entered

I assume your Form takes its data from another Tbl, or you are entering data
to another Tbl via this form, in which case you create a lookup field to the
Journey Tbl that shows the combined journey, You store the Mileage Tbl
IDCode, in this Tbl and not the journey text.
In a Form field the mileage FormField is populated from this lookup. You do
not store the mileage in this new Tbl.
These two Tbles have a relationship based on the Mileage IDCode, thus the
actual mileage can be displayed 'on the fly'
Access Help gives details of various 'lookup' type controls and how to
display one set of data whilst storing other data in a field.
Hope this gives you a hint
 
J

JoshuaGHarvey

I guess I dont understand.. and should take an Access class.. lol.
Thought this would be easier.. thanks
 
D

David F. Cox

I assume that you are not worried about minor differences caused by one-way
systems.

In which case you could only have two fields, towns and distance.

Towns = Name1 XOR Name2
distance

David F. Cox
 
J

JoshuaGHarvey

ill try that and see what i come up with!

I assume that you are not worried about minor differences caused by one-way
systems.

In which case you could only have two fields, towns and distance.

Towns = Name1 XOR Name2
distance

David F. Cox
Click to expand...
 
Top