Rewrite query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ielmrani - 24 Apr 2008 16:27 GMT

Hi,
is there a way to rewrite this code to make it short. The query won't run
because it max the # of characters allowed. sorry it's written in caps.
thanks in advance:

BASE ADMIN: IIf(CDPINC_ARIBD!IDACCTREV="5021-0PENSN-QTV" Or CDPINC_ARIBD!
IDACCTREV="5021-0PENSN-SAV" Or CDPINC_ARIBD!IDACCTREV="5021-0PENSN-ANV" Or
CDPINC_ARIBD!IDACCTREV="5021-0PENSN-RSA" Or CDPINC_ARIBD!IDACCTREV="5021-
0PENSN-PER" Or CDPINC_ARIBD!IDACCTREV="5021-0PENSN-DOC" Or CDPINC_ARIBD!
IDACCTREV="5021-0PENSN-SDI" Or CDPINC_ARIBD!IDACCTREV="5021-0PENSN-SVC" Or
CDPINC_ARIBD!IDACCTREV="5020-0PENSN-QTV" Or CDPINC_ARIBD!IDACCTREV="5020-
0PENSN-STQ" Or CDPINC_ARIBD!IDACCTREV="5020-0PENSN-MSC" Or CDPINC_ARIBD!
IDACCTREV="5020-0PENSN-PER" Or CDPINC_ARIBD!IDACCTREV="5020-0PENSN-DOC" Or
CDPINC_ARIBD!IDACCTREV="5020-0PENSN-SDI" Or CDPINC_ARIBD!IDACCTREV="5020-
0PENSN-SVC",CDPINC_ARIBD!AMTTXBL,0)

Reply to this Message

KARL DEWEY - 24 Apr 2008 16:52 GMT

The easy way is to build a table containing all the combination of
CDPINC_ARIBD!IDACCTREV that you are looking for, add that table in your
query, add the table field as criteria for CDPINC_ARIBD!IDACCTREV.

Additional if the list is subject to change add a Yes/No field for active or
some such. In the query add the Yes/No field in the design view grid as an
output field with a criteria of -1 (minus one) for active.

Signature

KARL DEWEY
Build a little - Test a little

> Hi,
> is there a way to rewrite this code to make it short. The query won't run
[quoted text clipped - 11 lines]
> CDPINC_ARIBD!IDACCTREV="5020-0PENSN-SDI" Or CDPINC_ARIBD!IDACCTREV="5020-
> 0PENSN-SVC",CDPINC_ARIBD!AMTTXBL,0)

Reply to this Message

ielmrani - 24 Apr 2008 16:59 GMT

Thanks Karl. I'll give it a try.

>The easy way is to build a table containing all the combination of
>CDPINC_ARIBD!IDACCTREV that you are looking for, add that table in your
[quoted text clipped - 8 lines]
>> CDPINC_ARIBD!IDACCTREV="5020-0PENSN-SDI" Or CDPINC_ARIBD!IDACCTREV="5020-
>> 0PENSN-SVC",CDPINC_ARIBD!AMTTXBL,0)

Reply to this Message

Marshall Barton - 24 Apr 2008 17:17 GMT

>is there a way to rewrite this code to make it short. The query won't run
>because it max the # of characters allowed. sorry it's written in caps.
[quoted text clipped - 10 lines]
>CDPINC_ARIBD!IDACCTREV="5020-0PENSN-SDI" Or CDPINC_ARIBD!IDACCTREV="5020-
>0PENSN-SVC",CDPINC_ARIBD!AMTTXBL,0)

I'm going crosseyed looking at that, but that sequence of OR
comparisons would be shorter if you used the IN operator:

BASE ADMIN: IIf(CDPINC_ARIBD!IDACCTREV In("5021-0PENSN-QTV",
    "5021-0PENSN-SAV". "5021-0PENSN-ANV", "5021-0PENSN-RSA",
    "5021-0PENSN-PER", "5021-0PENSN-DOC", "5021-0PENSN-SDI",
    "5021-0PENSN-SVC", "5020-0PENSN-QTV", "5020-0PENSN-STQ",
    "5020-0PENSN-MSC", "5020-0PENSN-PER", "5020-0PENSN-DOC",
    "5020-0PENSN-SDI", "5020-0PENSN-SVC")
    ,CDPINC_ARIBD!AMTTXBL, 0)

Signature

Marsh
MVP [MS Access]

Reply to this Message

ielmrani - 24 Apr 2008 17:53 GMT

Thank you. It worked.

>>is there a way to rewrite this code to make it short. The query won't run
>>because it max the # of characters allowed. sorry it's written in caps.
[quoted text clipped - 12 lines]
> "5020-0PENSN-SDI", "5020-0PENSN-SVC")
> ,CDPINC_ARIBD!AMTTXBL, 0)

Reply to this Message

Marshall Barton - 24 Apr 2008 20:38 GMT

Note that this approach is kind of a quick and dirty way of
doing things. In general, you should avoid imbedding data
values in code or queries,

Karl's idea of using a table with the values you want to
match and using a Join in the query to determine the matched
and unmatched fields is both more efficient and easier to
maintain over the long term.

Signature

Marsh
MVP [MS Access]

>Thank you. It worked.
>
[quoted text clipped - 14 lines]
>> "5020-0PENSN-SDI", "5020-0PENSN-SVC")
>> ,CDPINC_ARIBD!AMTTXBL, 0)

Reply to this Message

ielmrani - 24 Apr 2008 21:15 GMT

Yes, I also tried Karl's idea and it worked. Thank you guys.

>Note that this approach is kind of a quick and dirty way of
>doing things. In general, you should avoid imbedding data
[quoted text clipped - 9 lines]
>>> "5020-0PENSN-SDI", "5020-0PENSN-SVC")
>>> ,CDPINC_ARIBD!AMTTXBL, 0)

Reply to this Message