Limit data on Report

[Bagel]

I am a novice at access so please explain in the easiest way possible if possible.

I have a Table called "Citys"

I have a form with two combo boxes on it, Both with the same data "CityName"

I would like to be able to limit the data on my report so that only the citys between the two combo boxes appear on my report, example;

I select "Auckland" on the top combo box which is named "CityNameFrom" and select "Melbourne" on the second combobox which is called "CityNameTo", this would print only city's from Auckland to Melbourne and all others citys in between.

What would be my best way to do this as I need to apply this to most of my tables of information.

If anybody can help me it would be greatly appreciated but please include the code I will need to put in as I have a fairly basic database and knowledge of Access

Thank you in advance

 

[llkhoutx]

Use a query, with the combo boxes (USING AND OR THE In fucntion) as criteria, as the record source for your report.

 

[Bagel]

Thank you llkhoutx

How would the queery basically read?

Regards

Bagel

 

[llkhoutx]

The attached zipped Access 97 mdb illustrates your problem.

Note the table, the form and its VBA, the query & the report.

I have assumed you merely want the alphabetical names between two cities, if you want cities along a rounte between two cities, that's another problem.

Note that the query "QueryName" has to exist when the code is run. The code cannot overwrite it, but deletes it and then creates it. I didn't add code to see if it exists, I have assumed it does.

I have also assumed that the cities table has an index. Unfortunately, a query won't allow the column desination in the criteria (it thinks that its a function), therefore, I save the actual city name in a textbox. The combo box I wrote returns an index. I could have just as easily, made the city name the bound column of the combobox, but didn't as I never code that way. I seldom, if ever, names, virtually always using indices.

Note too that the query is the recordsource for the report. A report's recorsource can't otherwise be changed when it's opened.

This ought to get you running. Good luck.

 

[Bagel]

Thanks llkhoutx, your a champion.
I have hours on the net looking for solutions.

Thanks again

Regards

Bagel

 

[Bagel]

Hi Pat

I have tried what you suggested but this did not seam to work, is there something else I need to do?