Query Problem

Hi,
I have this query but is not working.

SELECT
BKAR_INV_NUM,
BKAR_INV_SONUM,
BKAR_INV_INVCD,
BKAR_INV_INVDTE,
BKAR_INV_CUSCOD
FROM BKARINV
WHERE (((BKAR_INV_INVCD)=' ') AND ((BKAR_INV_CUSCOD)="SCOENE0100")) OR
((BKAR_INV_INVCD)="X"));

I need it to show all records for SCOENE0100 that the BKAR_INV_INVCD are
either "X" or " "

Thank you all

On Mon, 22 Feb 2010 11:08:04 -0700, "Bre-x" wrote:

Hi,
I have this query but is not working.

SELECT
BKAR_INV_NUM,
BKAR_INV_SONUM,
BKAR_INV_INVCD,
BKAR_INV_INVDTE,
BKAR_INV_CUSCOD
FROM BKARINV
WHERE (((BKAR_INV_INVCD)=' ') AND ((BKAR_INV_CUSCOD)="SCOENE0100")) OR
((BKAR_INV_INVCD)="X"));

I need it to show all records for SCOENE0100 that the BKAR_INV_INVCD are
either "X" or " "

Thank you all


Access trims trailing blanks, so (unless you've gone to some pretty extreme
lenghts) BKAR_INV_INVCD will never equal a single blank character; in
addition, unless you have changed the default Allow Zero Length property on
the field, it won't contain an empty string "" either: it will be NULL.

Try

WHERE (((BKAR_INV_INVCD)='X" OR (BKAR_INV_INVCD) IS NULL) AND
((BKAR_INV_CUSCOD)="SCOENE0100"))

or, more compactly,

WHERE ((NZ(BKAR_INV_INVCD, "X")="X") AND ((BKAR_INV_CUSCOD)="SCOENE0100"))

--

John W. Vinson [MVP]

Thanks!!!


"John W. Vinson" wrote in message
...
On Mon, 22 Feb 2010 11:08:04 -0700, "Bre-x" wrote:

Hi,
I have this query but is not working.

SELECT
BKAR_INV_NUM,
BKAR_INV_SONUM,
BKAR_INV_INVCD,
BKAR_INV_INVDTE,
BKAR_INV_CUSCOD
FROM BKARINV
WHERE (((BKAR_INV_INVCD)=' ') AND ((BKAR_INV_CUSCOD)="SCOENE0100")) OR
((BKAR_INV_INVCD)="X"));

I need it to show all records for SCOENE0100 that the BKAR_INV_INVCD are
either "X" or " "

Thank you all


Access trims trailing blanks, so (unless you've gone to some pretty
extreme
lenghts) BKAR_INV_INVCD will never equal a single blank character; in
addition, unless you have changed the default Allow Zero Length property
on
the field, it won't contain an empty string "" either: it will be NULL.

Try

WHERE (((BKAR_INV_INVCD)='X" OR (BKAR_INV_INVCD) IS NULL) AND
((BKAR_INV_CUSCOD)="SCOENE0100"))

or, more compactly,

WHERE ((NZ(BKAR_INV_INVCD, "X")="X") AND ((BKAR_INV_CUSCOD)="SCOENE0100"))

--

John W. Vinson [MVP]