There's an error in that line somewhere.
Perhaps you can add:
Debug.Print strWhere
Then when it fails, open the Immediate Window (Ctrl+G) and see what came
out. Make sure the brackets match, and the expression makes sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
Thanks for your patience and help. I changed the strWhere clause as
you
suggested and it compiles; however, now when I try to open the form I
am
getting the following error:
Missing ), ], or Item in query expression ‘NOT EXISTS (SELECT
tbl_trans_buyer_seller.RecdID FROM tbl_trans_buyer_seller WHERE
(tbl_trans_buyer_seller.TransID = tbl_property.TransID) AND
(tbl_trans_buyer_seller.Last = Me.ClientLast)’.
I created a query with the same fields and the SQL view is:
SELECT tbl_trans_buyer_seller.RecdID, tbl_trans_buyer_seller.TransID,
tbl_trans_buyer_seller.Last
FROM tbl_property INNER JOIN tbl_trans_buyer_seller ON
tbl_property.TransID
= tbl_trans_buyer_seller.TransID
WHERE (((tbl_trans_buyer_seller.Last)="Mendrin"));
Mendrin being the Criteria for [ClientLast]
Any suggestions?
Also, I appreciate your comment regarding 'Last' not being a good field
name. Unfortunately, the application was developed 9 or 10 years ago
and
I
am trying to clean up a nmber of the unconventional methods he used.
The
application is quite large and somewhat complex and trying to change a
field
name at this time would probably be a disaster... and yes, Last is a
text
field which should have been named LName.
Thanks again
Carol
:
You need to concatenate the value from the control into the string.
If Last is a Number type field when you open tbl_trans_buyer_seller in
design view, try:
strWhere = "NOT EXISTS (SELECT tbl_trans_buyer_seller.RecdID FROM
tbl_trans_buyer_seller " & _
"WHERE (tbl_trans_buyer_seller.TransID = tbl_property.TransID) AND
(tbl_trans_buyer_seller.[Last] = " & Me.ClientLast & ") AND "
If it is a Text field, you need extra quotes:
strWhere = "NOT EXISTS (SELECT tbl_trans_buyer_seller.RecdID FROM
tbl_trans_buyer_seller " & _
"WHERE (tbl_trans_buyer_seller.TransID = tbl_property.TransID) AND
(tbl_trans_buyer_seller.[Last] = """ & Me.ClientLast & """) AND "
For an explanation of the quotes, see:
http://allenbrowne.com/casu-17.html
BTW, Last is a reserved word in SQL, so not a good field name. Square
brackets (as above) will help, but you probably want to avoid using
names
with special meaning as field names. Here's a list of the names to
avoid:
http://allenbrowne.com/AppIssueBadWord.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Thanks for the suggestion. I am pretty much a novice at VB, but am
trying
to
learn as I go. Anyway, I read your article on subquery basics and am
still
confused on how to add it to the strWhere filter. This is what I
currently
have:
If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & "([TransStat] Like """ & Me.txtStatus &
"*"")
AND "
End If
If Not IsNull(Me.ClientLast) Then
strWhere = "NOT EXISTS (SELECT tbl_trans_buyer_seller.RecdID
FROM
tbl_trans_buyer_seller " & _
"WHERE (tbl_trans_buyer_seller.TransID =
tbl_property.TransID)
AND (tbl_trans_buyer_seller.Last = Me.ClientLast) AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailing " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
I have omitted a number of fields above 'txtstatus'. I have a
feeling
that
I am probably missing brackets and/or have the quotes all wrong.
Thanks in advance for any help you can give me.
:
Your options for filtering on a field in a different table are:
a) use a query that includes the desired table,
b) use a subquery in the Filter of the form.
Since you already tried (a), here's some info on subqueries:
http://allenbrowne.com/subquery-01.html#Search
In general, I would not expect a subquery to be faster than a
query.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I have adapted Allen's search to a company database which greatly
improved the search function. The search is currently pulling
records
from 'tbl_property' and I have been asked to include the client
name
in the search criteria. The client information is in
'tbl_trans_buyerseller'
and is linked with 'TransID' field. I have tried using a query as
the
record source with no success. Much too slow. Is there a method
of
adding a 2nd table to this search function?
FYI - BE is SQL2000 and FE (Access 2000) resides on Terminal
servers using runtime.
