INLIST()???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I am trying to run a query on that I need to only select
certain types of "transactions". I typically would select WHERE in the
function row under the field in the query, then type
="XXXXXXXX"
="YYYYYYYYY"
="ZZZZZZZZZ"
and so on..
The only problem is that there are so many in the list that it violates the
1,024 string rule for Access. I know there is a better way to do it, I'm
just not sure. Can someone help me out please???????????

Thanks in advance.....

AJ
 
On Mon, 15 Jan 2007 18:20:00 -0800, AJ Norris <AJ
I have a table that I am trying to run a query on that I need to only select
certain types of "transactions". I typically would select WHERE in the
function row under the field in the query, then type
="XXXXXXXX"
="YYYYYYYYY"
="ZZZZZZZZZ"
and so on..
The only problem is that there are so many in the list that it violates the
1,024 string rule for Access. I know there is a better way to do it, I'm
just not sure. Can someone help me out please???????????

Thanks in advance.....

AJ
Click to expand...

You can save a little bit by using the IN clause: a criterion of

IN("XXXXXXXX", "YYYYYYY", "ZZZZZZZZ")

but you may want to go the extra step, and actually create a Table
with (perhaps) just one field for all the desired criteria; simply
join this table to your query on the transaction type.

John W. Vinson[MVP]
 
Thanks John,

The first option may work, I'll have to try it out and see. The extra step
option is not that clear to me.
Are you suggesting a one column table containing all of the transactions
that I need?

AJ
 
Are you suggesting a one column table containing all of the transactions
that I need?
Click to expand...

Yes. This could be a "scratch" table that you empty out and then
reenter another set of transactions; or if you have several queries,
each with a (more or less) stable set of transactions, you could use a
two-column table with a set identifier in one column and the
corresponding transactions in the other.

John W. Vinson[MVP]
 
Hi AJ,

I would use “NOT INâ€. For example,

Your Data in transactions is - A,B,C,D ….to X,Y,Z
You want input E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z
But not A,B,C,D

SELECT transactions FROM “TableName†WHERE transactions NOT IN (“Aâ€, “Bâ€,“Câ€,
â€Dâ€)

It will be much shorter than to fill from E to Z.
 
Back
Top