Query help

blazzzercat via VBMonster · Joined: 04 Oct 2007 Posts: 1

Greetings,

I need to write a query that looks like:

[Trans Date]<{2006-09-30} .And. PAYTO="6704aa" .Or. PAYTO="1337aa" .Or. PAYTO
etc.......

There are over 400 "PAYTO" codes I need to include. These codes are contained
in a Excel worksheet in column 2. Is there a script genius on this forum that
can help me with a VBA script to build this query so I won't have to type out
every one of them?

Thanks so much in advance.

--
Message posted via VBMonster.com
http://www.vbmonster.com/Uwe/Forums.aspx/vb-syntax/200709/1

Archived from group: microsoft>public>vb>syntax

MikeD · Joined: 04 Oct 2007 Posts: 3348

"blazzzercat via VBMonster.com" wrote in message @uwe...
> Greetings,
>
> I need to write a query that looks like:
>
> [Trans Date]<{2006-09-30} .And. PAYTO="6704aa" .Or. PAYTO="1337aa" .Or.
> PAYTO
> etc.......
>
> There are over 400 "PAYTO" codes I need to include. These codes are
> contained
> in a Excel worksheet in column 2. Is there a script genius on this forum
> that
> can help me with a VBA script to build this query so I won't have to type
> out
> every one of them?
>

I assume you can write the code to loop through the rows to get all the
value from column 2 of this Excel spreadsheet....if not, you need to ask for
help on that in an Excel newsgroup. Frankly, no part of this question really
belongs in a VB newsgroup.

As far as the SQL, use the IN clause. You'd have something like this:

WHERE [Trans Date]<{2006-09-30} AND PAYTO IN ('6704aa' , '1337aa', ....)

If you need help with the SQL aspect of this, you should ask in a SQL
newsgroup. However, 400 expressions for an IN clause is an awful lot....I
don't know if it can handle that many. By any chance, do all of the PAYTO
values happen to end in "aa"? If so, you could use a wildcard. Even if some
of them end with other characters, using wildcards would probably be the way
to go. IOW, maybe something like this:

WHERE [Trans Date]<{2006-09-30} AND (PAYTO LIKE '%aa' OR PAYTO LIKE '%bb' OR
PAYTO LIKE '%cc')

--
Mike
Microsoft MVP Visual Basic