Conditions

[charbrad]

I have a query that has two conditions in my where statement, for example

WHERE CUSTOMER = 'ABCDE' AND ITEMNMBR = '1234'

My problem is that if it finds ITEMNMBR = '5678' for CUSTOMER = 'ABCDE' then it should appear in the report and it does not...can someone tell me what I am doing wrong?

 

[xlbo]

Are you running

WHERE CUSTOMER = 'ABCDE' AND ITEMNMBR = '1234'

and expecting ITEMNMBR = '5678' to appear? If so then it won;t because it does not satisfy the condition.....

If not and you are just using 2 different examples can you

a: confirm that the query returns data in (for example) query analyser

b: check whether you have any filters applied to either the dataset or the table in the report you are displaying the data in

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RiverGuy]

That where statement will only return records where Customer = 'ABCDE' AND Itemnmbr = '1234.'

Are you only after all records for a particular customer? If so, then your where clause should just be WHERE CUSTOMER = 'ABCDE'

Or, if you are after all records for a particular customer -OR- records with a particular item number, then your where clause should look more like the following:
WHERE CUSTOMER = 'ABCDE' OR ITEMNMBR = '1234'

 

[bburnell]

In your query, it will only show those records that ITEMNMBR = "1234" *AND* CUSTOMER = "ABCDE." If you'd like it to show both, you'd have to do something like:

WHERE (CUSTOMER = 'ABCDE') AND (ITEMNMBR = '1234' OR ITEMNMBR = '5678')

I'm not sure that's what you asked though.
Brett

--------------------------
Web/.net Programmer & DBA
Central PA

 

[charbrad]

Sorry I made a mistake in my code, it should read:

WHERE CUSTOMER <> 'ABCDE' AND ITEMNMBR <> '1234'

so if it finds the above criteria it should not return any records, but if it finds

CUSTOMER = 'ABCDE' AND ITEMNMBR = '5678'

then it should return the records and it does not.

I hope this is a little clearer.

 

[bburnell]

Your code, the way it is written, will never show any CUSTOMER = 'ABCDE.' This statement will only return the records that do not equal BOTH situations. You could possibly use an if...then...else structure for exceptions.

Maybe you wanted:

WHERE CUSTOMER <> 'ABCDE' OR ITEMNMBR <> '1234'

?

--------------------------
Web/.net Programmer & DBA
Central PA

 

[bburnell]

You could do this as well:

WHERE (CUSTOMER <> 'ABCDE' AND ITEMNMBR <> '1234') OR (CUSTOMER = 'ABCDE' AND ITEMNMBR = '5678')

--------------------------
Web/.net Programmer & DBA
Central PA

 

[xlbo]

So you are looking just to exclude 1 particular combination? In that case:

WHERE ( CUSTOMER <> 'ABCDE' AND ITEMNMBR <> '1234' )


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question