Access Query - a simple question, I think

  Mike D 11:06 29 Sep 05

I'm looking at a very simple Access query, which asks for all chairs priced between £70 and £200, with a second criteria of operator or executive chairs. In the Table in question the price field is formatted as currency. When the query is run it finds chairs at £69.99 up to £199.99 in prices and either operator of executive.

Why will it not do as it's told and disregard the chairs at £69.99?

What remains of my hair is currently in grave danger - please help!


  Rigga 12:35 29 Sep 05

It may help to post the query.

Anyway, I would try instead of saying > 70 try > 70.00 I say this because if you try to compare an integer with a decimal it will round the decimal i.e. round 69.99 to give 70.


  Rigga 12:36 29 Sep 05

oops obv >= 70.00 or you'll miss the £70.00 ones.


  Mike D 13:34 29 Sep 05


Not sure how to post the query. I have tried two ways to get the query to run:

1: Criteria reads ">=70.00 AND <=200.00"

2: Criteria reads "Between 70.00 AND 200.00"

I've also tried both ways with the decimal places but I always end up with the same result, i.e all results are less that £200.00, with the lowest value being £69.99 (this, by the way is not the lowest value in the table).


  interzone55 14:39 29 Sep 05

This is the SQL code from my test query.

If you send me an email I'll mail you the test database

SELECT Table1.ChairModel, Table1.ChairName, Table1.ChairType, Table1.ChairPrice
FROM Table1
WHERE (((Table1.ChairType)="Operator" Or (Table1.ChairType)="Executive") AND ((Table1.ChairPrice) Between 70 And 200));


  Access genu 15:32 29 Sep 05

I would bet that the 69.99 one is an operator.

Yor query is returning ALL operator chairs and executive chairs between the values of 70 and 200.

Try. (I have not tested this)

Where (chairPrice BETWEEN 70 AND 200) AND

(ChairType = "Operator" OR ChairPrice = "Executive")

  Mike D 15:57 29 Sep 05

Thanks guys. I will have a look at all the suggestions, Access genu that looks more like it!

I will post a response ASAP, but this might be after the weekend.


  Mike D 09:10 30 Sep 05

Right folks, managed to look at this earlier than anticipated.

Alan your example worked a treat. Mind you, at first sight it looked identical to mine until I noticed one diffence on the formatting of the price field. Mine was shown as currency data type, but not in the format field at the bottom of the design view page.

Access genu, yours also worked and I will use this as an alternative method when showing my students this example again.

Thanks muchly


This thread is now locked and can not be replied to.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Switch price…

1995-2015: How technology has changed the world in 20 years

Best Photoshop video tutorials: 8 video tutorial websites for Photoshop

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…