Prev: Having problems understanding sub-form selections and uses ofcomb
Next: Need help sending mail!
From: Bre-x on 22 Feb 2010 13:08
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


From: John W. Vinson on 22 Feb 2010 13:31
On Mon, 22 Feb 2010 11:08:04 -0700, "Bre-x" <cholotron(a)hotmail.com> 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]
From: Bre-x on 22 Feb 2010 14:41
Thanks!!!


"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:h5j5o5tb4lc3r5vmqe5ao8k3k0qc4b47fb(a)4ax.com...
> On Mon, 22 Feb 2010 11:08:04 -0700, "Bre-x" <cholotron(a)hotmail.com> 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]


 | 
Pages: 1
Prev: Having problems understanding sub-form selections and uses ofcomb
Next: Need help sending mail!