SQL(MS Access) syntax

Question

Trying to find all these that are not in warehouse number 3 and unit price less than 100. Whats wrong with my code?

select part_number,
           part_description,
           Units_on_Hand,
           Unit_price,
           Warehouse_number
from part
where unit_price >= 100
and not in warehouse_number = 3;

Hi Clay - you might want to read How to Ask and minimal reproducible example - specifically for this question you could probably have got a better response by including whatever error you're seeing, and referring to that error in the question title (we can tell it's sql/access from the tags, even on the questions page). — Jeff
– Jeff, Commented Dec 17, 2016 at 20:41

Gordon Linoff · Accepted Answer · 2016-11-03 22:24:02Z

2

The problem is not in. You can do:

where unit_price >= 100 and
      not (warehouse_number = 3);

Or:

where unit_price >= 100 and
      warehouse_number not in (3);

Or:

where unit_price >= 100 and
      warehouse_number <> 3;

These are all equivalent. The last would be the more "typical" way to write this for 1 warehouse. The second would be the more typical way if there was more than one warehouse.

answered Nov 3, 2016 at 22:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Clay Over a year ago

Thank you i tried those and its still giving me an error message.

Clay Over a year ago

select part_number, part_description, Units_on_Hand, Unit_price, Warehouse_number from part where unit_price >= 100 and warehouse_number not in (3);

Clay Over a year ago

data type mismatch in criteria expression

Parfait Over a year ago

What is the data type of warehouse_number?

Gordon Linoff Over a year ago

@Clay . . . That would suggest that either unit_price or warehouse_number are not the numbers they appear to be.

|

demo · Accepted Answer · 2016-11-04 01:13:28Z

-1

SELECT part_number, part_description, Units_on_Hand, Unit_price, Warehouse_number 
FROM part 
Where unit_price >= 100 AND warehouse_number NOT IN (3);

edited Nov 4, 2016 at 1:13

demo

6,29519 gold badges84 silver badges162 bronze badges

answered Nov 3, 2016 at 22:23

Nick

383 bronze badges

2 Comments

Clay Over a year ago

still receiving error message with that to and i dont get why. I appreciate the effort though thank you

Clay Over a year ago

Thank you sorry i had a simple data type mismatch

Collectives™ on Stack Overflow

SQL(MS Access) syntax

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related