Find posts which are between two field values

[Vallan]

Hello.


My mind i stuck with this problem wich i hope someone easily could give me a hint how to make it possible? :banghead:


I have two unbound tables.

One table contents

From........
Too..........
Distance...

The other table contents

Company..
Min_km....
Hi_km......
Price........


What my intention is to make a form that find wich companies who drive the distance and price.

For example..... I choose a journey from London to Leeds and distance is 123 km

When i push a searchbutton then i get what companies drives the distance and the price?

I cant get how to take the distance of the trip and search the table 2 where the distance is between Min_km and Hi_km and the outcome is

DHL want 123 pounds for the trip
Trans want 140 pounds for the trip.


What i need help with is how to search....

If distance choosen by table one is =< than Min_km or => Hi_km then get the prices.

I hope i have explained my problem good enough?

Kind regards

Vallan

 

[jzwp22]

Re: Find posts wich it between two fieldvalues

Since the results might include multiple companies, I would recommend using a subform based on a query that filters the data from the second table. The key would be the filtering of course.

You did not provide any example data for table two, but I would guess that it may look something like this

ID|Company|Min_km|Hi_km|Price
1|DHL|0|50|75
2|DHL|50|100|100
3|DHL|100|200|123
4|Trans|0|100|100
5|Trans|100|200|140
6|Tran|200|300|175

If distance choosen by table one is =< than Min_km or => Hi_km then get the prices

Also, I think the above is incorrect. If the distance is greater than or equal to Min_km AND less than or equal to Hi_km then pull the record. We would need to translate this into a WHERE clause of a query. Something like this:

WHERE distance>=Min_km AND distance<=Hi_km

The best way to do this is to create the query on the fly when the button is clicked and then make that query the record source for the subform

The code behind the button would look something like this

Dim mySQL as string
mySQL="Select Company, Price FROM table2"
mySQL= mySQL & " WHERE " & me.distance & ">=Min_km AND " & me.distance & "<=Hi_km"

me.subformname.form.recordsource=mySQL

I worked up an example database; open the form frmSearch and see if it accomplishes what you are after.

 

[Vallan]

Thank you for your help.

I have developed it a bit more but got stuck there also.

In table 1 i have stored all the distance to different places from my locations. (we have several units around the country.)

In the form i have 2 unbound comboboxes cascading.

So if i chose company 1 that is placed in London combobox 1, then in combobox i only get the destinations that are for London.

What i need help with is how to lookup the distance in a textbox from the choice in combobox 2.

From there i then use the solution that got from jswp22 above.

Thanks in advance.

Mattias

 

[jzwp22]

I would include the distances in the second combo box and then reference that in the code I provided. So instead of referencing a textbox (me.distance) with the distance, reference the column of the combo box that contains the distance value.

me.combobox2name.column(x)

where x=the column number that holds the distance. Access starts counting columns in a combo box at zero not one. So if your row source of combo box 2 looks like this:

SELECT ID, From, To, Distance

The distance is in column 3.