AND / OR in a form

[Jeroen]

Dear,

I want to make my query as flexible as possible and would like to make the
and/or relation of my query flexible .
I would like to have a text field on a form where I can specifiy the and/or
relation of my query. Can someone help me out with the code ?
For example:

SELECT Table1.name, Table2.company, Table2.Street, Table2.city, Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number = Table3.number) INNER JOIN
Table2 ON (Table1.number = Table2.number) AND (Table3.number = Table2.number)
WHERE (((Table2.company)=[forms]![form1]![text1]) AND
((Table1.selectie)=[forms]![form1]![text2]));

I want that I can fill out this last AND in a form (text field) (this would
also make it possible to place there an OR

Thanks in advance

 

[Jeff Boyce]

Jeroen

Just as your SQL statement already includes references to the values in text
boxes on your form for selection criteria, you could also refer to an AND/OR
choice on your form to provide the connector in your SQL statement.

 

[Jeroen]

Thanks Jeff for your answer.. I know I should be able to replace the and but
I always get an error with the following code

SELECT Table1.name, Table2.company, Table2.Street, Table2.city, Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number=Table3.number) INNER JOIN
Table2 ON (Table1.number=Table2.number) AND (Table3.number=Table2.number)
WHERE (((Table2.company)=[forms]![form1]![text1])
([forms]![form1]![textand]) ((Table1.selectie)=[forms]![form1]![text2]));

hope someone can find the error.
Thanks

Jeroen

Just as your SQL statement already includes references to the values in text
boxes on your form for selection criteria, you could also refer to an AND/OR
choice on your form to provide the connector in your SQL statement.

--
Good luck

Jeff Boyce
<Access MVP>

Dear,

I want to make my query as flexible as possible and would like to make the
and/or relation of my query flexible .
I would like to have a text field on a form where I can specifiy the and/or
relation of my query. Can someone help me out with the code ?
For example:

SELECT Table1.name, Table2.company, Table2.Street, Table2.city, Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number = Table3.number) INNER JOIN
Table2 ON (Table1.number = Table2.number) AND (Table3.number = Table2.number)
WHERE (((Table2.company)=[forms]![form1]![text1]) AND
((Table1.selectie)=[forms]![form1]![text2]));

I want that I can fill out this last AND in a form (text field) (this would
also make it possible to place there an OR

Thanks in advance

 

[John Vinson]

SELECT Table1.name, Table2.company, Table2.Street, Table2.city, Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number=Table3.number) INNER JOIN
Table2 ON (Table1.number=Table2.number) AND (Table3.number=Table2.number)
WHERE (((Table2.company)=[forms]![form1]![text1])
([forms]![form1]![textand]) ((Table1.selectie)=[forms]![form1]![text2]));

hope someone can find the error.

You cannot pass an operator such as AND or OR as a parameter - only
the values to be searched. I'd suggest that your best bet may be to
use VBA code to construct the SQL string.

You *could* use the following query:

SELECT Table1.name, Table2.company, Table2.Street, Table2.city,
Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number=Table3.number)
INNER JOIN Table2 ON (Table1.number=Table2.number) AND
(Table3.number=Table2.number)
WHERE ((Table2.company)=[forms]![form1]![text1] AND
Table1.selectie)=[forms]![form1]![text2]) AND
[forms]![form1]![textand] = "AND")
OR
((Table2.company)=[forms]![form1]![text1] OR
Table1.selectie)=[forms]![form1]![text2]) AND
[forms]![form1]![textand] = "OR") ;

This may be a lot less efficient though!

John W. Vinson[MVP]

 

[Jeff Boyce]

Thanks, John.

I guess I had assumed that the SQL statement was being "created"
dynamically, so the use of the form-based operator should be able to work,
no?

Jeff Boyce
<Access MVP>

SELECT Table1.name, Table2.company, Table2.Street, Table2.city, Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number=Table3.number) INNER JOIN
Table2 ON (Table1.number=Table2.number) AND (Table3.number=Table2.number)
WHERE (((Table2.company)=[forms]![form1]![text1])
([forms]![form1]![textand]) ((Table1.selectie)=[forms]![form1]![text2]));

hope someone can find the error.

You cannot pass an operator such as AND or OR as a parameter - only
the values to be searched. I'd suggest that your best bet may be to
use VBA code to construct the SQL string.

You *could* use the following query:

SELECT Table1.name, Table2.company, Table2.Street, Table2.city,
Table2.company
FROM (Table1 INNER JOIN Table3 ON Table1.number=Table3.number)
INNER JOIN Table2 ON (Table1.number=Table2.number) AND
(Table3.number=Table2.number)
WHERE ((Table2.company)=[forms]![form1]![text1] AND
Table1.selectie)=[forms]![form1]![text2]) AND
[forms]![form1]![textand] = "AND")
OR
((Table2.company)=[forms]![form1]![text1] OR
Table1.selectie)=[forms]![form1]![text2]) AND
[forms]![form1]![textand] = "OR") ;

This may be a lot less efficient though!

John W. Vinson[MVP]

 

[John Vinson]

Thanks, John.

I guess I had assumed that the SQL statement was being "created"
dynamically, so the use of the form-based operator should be able to work,
no?

Good conclusion - but based on an incorrect but reasonable assumption!

John W. Vinson[MVP]