Query a range of numbers

[Vaimpir]

I Need to create a query that returns values between certain prices in a field.

Thanks,

Steven

 

[pbaldy]

If I'm understanding correctly:

SELECT ...
FROM ...
WHERE FieldName BETWEEN Price1 AND Price2

 

[Vaimpir]

Do I write this into a module and set the criteria of the query to that module?

 

[pbaldy]

Doubt it. What is it you're trying to do exactly?

 

[Vaimpir]

I have a table for properties for sale. I want to make a search for certain criteria of homes. Such as, sales price location bedroom baths, ect. I want the search to be able to search a range of certain fields like range of bedrooms, range of baths, range of sale prices. As well as search a certain location, whether or not there is a pool, ect. Does that help you any?

Steven

 

[Mile-O]

certain fields like range of bedrooms, range of baths, range of sale prices.

Can you specify your table structure?

 

[Vaimpir]

Table: Property Detail

Fields:
Property address
Property City
Property State
Property Zip
Sales Price
Bedrooms
Baths
Pool (yes/no)
Square Footage
Homeowners Association (yes/no)

Does that help?

Steven

 

[Mile-O]

Ok, I thought you may have a heavily un-normalized table but it's okay. Although some property details really deserve tables of their own with only a foreign key remaining in that table (City and State).

In your SQL you'll need the IN clause for your criteria:

i.e.

SELECT *
FROM MyTable
WHERE Bathrooms IN (1, 2, 4);

where the query will return all houses that have 1, 2 or 4 bathrooms but not those with 3.

 

[Vaimpir]

How what if I want it to search for bathrooms between 1 and 3? And how do I code this into a query to make it work?

Steven

 

[Mile-O]

You need to build a QueryDef - there are examples if you search.

And this.