Search query in MS Access 97?

a_gajic posted this at 16:29 — 15th August 2001.

Joined: Aug 2001

Okay I'll cut to the chase. I have a piece of coursework at school in GCSE ICT to create a second user car database with queries.

I have created a table called: TBL Second User Cars.

In this table there are: ID, Manufacturer, Model, Registration Letter, Petrol/Diesel, Mileage, Price (£).

I also have a form where you can add, delete and modify records.

I want to create another form that will allow users to search the table for entries that they want. I would have drop down menus so that the user can search and the search would have more chance of bringing the results back.

Here is what I want..

-------

Manufacturer -> Ford, Renault, etc

Registration Letter -> A to Y

Petrol/Diesel -> Petrol/Diesel

Mileage -> 0-1000, 1001-2000 etc etc

Price -> Same as mileage really

-------

So then when they have filled in all of the required fields (could i put validation checks on these?) how would I get it so that it searches for the car in the table? Could I do it so that it prints the car(s) on the form to make it look nicer?

I think that to make the search I would have to create a button on the search form linking to a query but what would the code be for the query?

Thanks in advance guys.
Adam.

Peter J. Boettcher posted this at 17:11 — 15th August 2001.

They have: 812 posts

Joined: Feb 2000

Adam,

Search queries can become pretty complex, I'll try to keep it simple. Here's a sample stored procedure using your fields.

CREATE PROCEDURE dbo.CarSearch

@Manufacturer varchar(50),
@Registration varchar(50),
@Fuel varchar(20),
@MileageLow integer,
@MileageHigh integer,
@State varchar(50),
@PriceLow money,
@PriceHigh money

SET NOCOUNT ON

SELECT * FROM TBL_Second_Use_Cars
WHERE Manufacturer = @Manufacturer
AND Registration_Letter LIKE @Registration + '_'
AND Petrol/Diesel = @Fuel
AND Mileage BETWEEN @MileageLow AND @MileageHigh
AND Price BETWEEN @PriceLow AND @PriceHigh

You will have to modify this a little to work with Access but you should be able to see the logic from this example.

P.S. You should really have a separate table for the manufacturers, and then link that table to your existing table.

PJ | Are we there yet?
pjboettcher.com

a_gajic posted this at 18:13 — 19th August 2001.

They have: 71 posts

Joined: Aug 2001

thanks a lot peter your help was greatly appreciated

-----

I get an error and it highlight the word PROCEDURE on line 1..?

Peter J. Boettcher posted this at 12:34 — 20th August 2001.

They have: 812 posts

Joined: Feb 2000

It's probably doing that because you're not using a stored procedure, try taking that line, and everything up to and including "SET NOCOUNT ON" out.

PJ | Are we there yet?
pjboettcher.com