Query in VBA

[Guest]

Hi All,

I'm managing to get to grips with VBA for Excel but I now want to venture in
Access VBA but don't know where to start!

I need to produce a query that will use 15 criterion on the same field but
Access limits me to 9. Is there any way I can convert a query from the
design grid to VBA so I can see the code and just add more lines?

I know I couls just export it to Ecel and do it there but I would really
like to understand Access a bit better.

Any assistance greatfully received.

Paul

 

[Wayne Morgan]

To move a query from the design grid to VBA, change the design grid to SQL
view then copy and paste the SQL text into VBA.

 

[fredg]

Hi All,

I'm managing to get to grips with VBA for Excel but I now want to venture in
Access VBA but don't know where to start!

I need to produce a query that will use 15 criterion on the same field but
Access limits me to 9. Is there any way I can convert a query from the
design grid to VBA so I can see the code and just add more lines?

I know I couls just export it to Ecel and do it there but I would really
like to understand Access a bit better.

Any assistance greatfully received.

Paul

You are not limited to 9 criteria lines.
In Query Design view, click on
Insert + Rows

 

[John Vinson]

Hi All,

I'm managing to get to grips with VBA for Excel but I now want to venture in
Access VBA but don't know where to start!

I need to produce a query that will use 15 criterion on the same field but
Access limits me to 9. Is there any way I can convert a query from the
design grid to VBA so I can see the code and just add more lines?

Well... you don't need to go to VBA to do a Query; the basic language
for queries is SQL, not VBA. You can open the query in design view and
select View... SQL, or use the leftmost tool in the query design
toolbar to select SQL view, and edit the query there.

As noted elsethread, you're not limited to 15 OR lines; but if you
have that many OR's, you may want to use a different syntax, or even a
different table structure. The IN() operator in SQL is handy for this:
you can put

IN (34, 55, 89, 144, 233, 377, 610)

on the Criteria line to retrieve records with any of the listed
values. Use quotes around the values for a Text field.

Alternatively, you may be able to have a Join to another table with
the values you wish to find.

John W. Vinson[MVP]