Rewrite query

[ielmrani via AccessMonster.com]

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)

 

[KARL DEWEY]

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.

 

[ielmrani via AccessMonster.com]

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
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.

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)

 

[Marshall Barton]

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)

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)

 

[ielmrani via AccessMonster.com]

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 - 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)

 

[Marshall Barton]

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.
--
Marsh
MVP [MS Access]

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 - 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)

 

[ielmrani via AccessMonster.com]

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
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.

Thank you. It worked.

[quoted text clipped - 14 lines]