Problem with EXISTS clause in filter

[markmarko]

So, I've a form that gets 'filtered' ala Allen Brown's method filtering for a
field in a subform, which consists of concatenating an sqlString, and using
that sqlS as the form's recordsource.

I'd like to attempt to make a change, and actually use the forms filter
property instead.

The it works fine in some cases, but when the sqlString includes the EXISTS
clause, it delivers a syntax error.

When the sqlString containing the EXISTS clause is applied to the
recordsource (as mentioned above), it works. When I remove the word "WHERE"
and apply the string to the forms filter property, I get a syntax error.

So.... the question is this... Is it possible to use an sql EXISTS clause in
a forms filter property.

For reference, here is the WHERE clause (minus the word WHERE)...
(nz([Record-Orders-Sales].[OrderStatus]) = 2 OR
nz([Record-Orders-Sales].[OrderStatus]) = 3
OR nz([Record-Orders-Sales].[OrderStatus]) = 4 OR
nz([Record-Orders-Sales].[OrderStatus]) = 9)
AND ([Query-Junction-Cust_Account].[System] = 2)
AND (IIF (Exists (SELECT [Record-Orders-Install].[InstallOrderID]
FROM [Record-Orders-Install]
[Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID]) ,
Exists (SELECT [Record-Orders-Install].[InstallOrderID]
FROM [Record-Orders-Install]
[Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID]
AND [Record-Orders-Install].[JobStatusDate] = #6/25/2008#)
AND exists (
SELECT [Record-Orders-Install].[InstallOrderID]
FROM [Record-Orders-Install]
[Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID]
AND [Record-Orders-Install].[JobStatus] = 5 ),
[ScheduledDate] = #6/25/2008#))

 

[markmarko]

To simplify...

The WHERE clause (which includes an EXISTS clause) works find when applied
to the forms recordsource.

It does NOT work when the 'WHERE' is removed and applied to the forms filter
(ala Access help which states "The Filter property is a string expression
consisting of a WHERE clause without the WHERE keyword. For example, the
following Visual Basic code defines and applies a filter to show only
customers from the USA:")

 

[markmarko]

Oh my....

Well, I realized what was going wrong... It was a boneheaded error...

I had used REPLACE to remove the WHERE clause, which (duh) also removed it
from the WHERE cluases of the EXISTS predicate.

So, for those keeping score... You can use EXISTS in a forms filter!