Can I use 'Exists' in a findfirst criteria?

[markmarko]

I have a procedure that concatenates criteria to use with a 'Find' Button.
It's based on a similar procedure I've written for a Filter Button. (Filter
defines the forms recordsource, Find looks for a record within the
recordsource)

The Find procedure builds a string, then uses findnext & findfirst to
attempt to find records in the a recordset.clone.

One of the criteria, as it's used in the Filtering procedure, uses the SQL
clause 'EXISTS'. As I understand FindFirst, the criteria should be basically
the same format. The help file says "Criteria is a String used to locate the
record. It is like the WHERE clause in an SQL statement, but without the word
WHERE."

I've copied the code from the Filter procedure (which works) into the Find
procedure, modified to use the Find procedures variables, but it always
returns the error "unknown or invalid field reference"

Here's the piece of code:
If Nz(Me.[Filter-Phone]) > 0 Then
FindString = FindString & _
" AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Junction-Customers_Account#s].[JunctionID]" &
vbCrLf & _
" FROM [Junction-Customers_Account#s] " & vbCrLf & _
" WHERE [Junction-Customers_Account#s].[JunctionID] =
[Record-Orders-Sales].[AssociatedCustAcct#Junction] " & vbCrLf & _
" AND" & vbCrLf & _
" (exists " & vbCrLf & _
" (SELECT [Core-CustomerNames].ID" & vbCrLf & _
" FROM [Core-CustomerNames] " & vbCrLf & _
" WHERE [Core-CustomerNames].[ID] =
[Junction-Customers_Account#s].[CustomerLastName] " & vbCrLf & _
" AND [Core-CustomerNames].[HomePhone] Like ""*" &
Me.[Filter-Phone] & "*"") " & vbCrLf & _
" OR exists" & vbCrLf & _
" (SELECT DupePhone.ID" & vbCrLf & _
" FROM [Core-CustomerNames] as DupePhone " & vbCrLf & _
" WHERE DupePhone.[ID] =
[Junction-Customers_Account#s].[CustomerLastName] " & vbCrLf & _
" AND DupePhone.[AltPhone] Like ""*" &
Me.[Filter-Phone] & "*"") " & vbCrLf & _
" )" & vbCrLf & _
" )"
End If



And here's an example of how it appears in the immediate window:
exists
(
SELECT [Junction-Customers_Account#s].[JunctionID]
FROM [Junction-Customers_Account#s]
WHERE [Junction-Customers_Account#s].[JunctionID] =
[Record-Orders-Sales].[AssociatedCustAcct#Junction]
AND
(exists
(SELECT [Core-CustomerNames].ID
FROM [Core-CustomerNames]
WHERE [Core-CustomerNames].[ID] =
[Junction-Customers_Account#s].[CustomerLastName]
AND [Core-CustomerNames].[HomePhone] Like "*5865551212*")
OR exists
(SELECT DupePhone.ID
FROM [Core-CustomerNames] as DupePhone
WHERE DupePhone.[ID] =
[Junction-Customers_Account#s].[CustomerLastName]
AND DupePhone.[AltPhone] Like "*5865551212*")