Access Query - a simple question, I think

  Mike D 11:06 29 Sep 2005
Locked

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!

Mike

  Rigga 12:35 29 Sep 2005

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.

HTH. R.

  Rigga 12:36 29 Sep 2005

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

R.

  Mike D 13:34 29 Sep 2005

Rigga

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).

Mike

  interzone55 14:39 29 Sep 2005

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));

Alan

  Access genu 15:32 29 Sep 2005

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 2005

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

  Mike D 09:10 30 Sep 2005

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

Mike

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

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?