MS Query criteria for no range excluding one number 2

[deisehun]

I need to run a query with several fields.

I need to have a certain field >=620 and <=999, I also need it to not show 900

Whats the simplest way to display that in the criteria?

Thanks..

 

[SkipVought]

Hi,

...
Where [TheField] Between 620 And 999
  And [TheField] <>900

Skip,

_[red][/red]

 

[deisehun]

Thanks a lot

 

[deisehun]

In my query, I have the criteria written exactly as below:

Criteria Field: Status
Value: Between '620' And '999'
or: <>'900'

It's still showing all status though
I have also have the Cust no field as per below:

='Cust no'

I've also tried with no equals, with 'like' cust no - it's still showing all Cust no's

Is there something incredibly stupid I'm doing?

Thanks

 

[SkipVought]

Please post your entire SQL.

Skip,

_[red][/red]

 

[deisehun]

SELECT PRODDTA_F4211.SDDOCO, PRODDTA_F4211.SDDCTO, PRODDTA_F4211.SDMCU, PRODDTA_F4211.SDAN8, PRODDTA_F4211.SDAITM, PRODDTA_F4211.SDUOM, PRODDTA_F4211.SDUORG, PRODDTA_F4211.SDVR01, PRODDTA_F4211.SDDRQJ, PRODDTA_F4211.SDLTTR
FROM PRODDTA_F4211
WHERE (((PRODDTA_F4211.SDMCU)=' BP') AND ((PRODDTA_F4211.SDAN8)=10800) AND ((PRODDTA_F4211.SDLTTR) Between "620" And "999")) OR (((PRODDTA_F4211.SDLTTR)<>"900"));

--------------

Cheers!

 

[BaneNeo]

Just jumping in here it looks like your problem is with your last 'OR'

Change that to 'AND' and you should be good.

NOTE: take out the ''

Adrian

 

[deisehun]

Good man Adrian - well spotted!

Thanks to all.

 

[deisehun]

Sorry - false alarm - it's not showing 620 - any ideas?

Thanks..

 

[BaneNeo]

Well between should be inclusive of the end points as a control you could try to split of the between like this

(PRODDTA_F4211.SDLTTR >= '620' And PRODDTA_F4211.SDLTTR <= '999')

Which is essentially the same thing.

If it is possible could you post what the row for '620' should look like just to make sure that it doesn't fail any of the other checks in your where statement? (also include what each item is in the table ex. Date = 2007-01-01; Price = 200; ..etc.)

Also one other thing of note is that you are using double hashes around 620 and 999 try changing them to a single hash (looks like this: ' ) and see if that fixes it. (shouldn't matter for SQL server but Access may be less forgiving on the sytax stuff.)


One last point what is this supposed to do?
(((PRODDTA_F4211.SDMCU)=' BP')

Is it looking for a string with 9 spaces then a BP? if so you may want to replace it with:
(PRODDTA_F4211.SDMCU LIKE '%BP')
Which will allow for anything with a BP in the string.

NOTE: This is not advisable is you have a lot of values for this column with BP in it as it defeats the purpose of filtering.