PC Review


Reply
Thread Tools Rate Thread

Allen Browne's Search2000

 
 
Carol2901
Guest
Posts: n/a
 
      16th Jun 2009
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.

Any help would be greatly appreciated.
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      16th Jun 2009
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Carol2901" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.


 
Reply With Quote
 
 
 
 
Carol2901
Guest
Posts: n/a
 
      17th Jun 2009
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.

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Carol2901" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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.

>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      18th Jun 2009
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/Ap****ueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Carol2901" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> "Allen Browne" wrote:
>
>> 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
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Carol2901" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >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.

>>
>>


 
Reply With Quote
 
Carol2901
Guest
Posts: n/a
 
      18th Jun 2009
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

"Allen Browne" wrote:

> 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/Ap****ueBadWord.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Carol2901" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > "Allen Browne" wrote:
> >
> >> 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
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> >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.
> >>
> >>

>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      19th Jun 2009
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Carol2901" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Allen Browne" wrote:
>
>> 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/Ap****ueBadWord.html
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Carol2901" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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.
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Carol2901" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> >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.
>> >>
>> >>

>>
>>


 
Reply With Quote
 
Carol2901
Guest
Posts: n/a
 
      19th Jun 2009
Hi Allen,

Here is the debug.print -
([PropNum] Like "55*") AND ([StreetName] Like "Mead*") AND ([Archive] =
False) AND 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] = "Public")

and here is the code -
If Not IsNull(Me.ClientLast) Then
strWhere = 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

I do notice that there is a parenthesis in before SELECT but I don't find an
ending parenthesis. I have tried inserting one in what seem obvious places to
me but to no avail. I have never seen the term NOT EXISTS before and haven't
had time to research it but am assuming it means to look for a table other
than the current Record Source?

Thanks again.
Carol

"Allen Browne" wrote:

> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "Carol2901" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> > "Allen Browne" wrote:
> >
> >> 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/Ap****ueBadWord.html
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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.
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> 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
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> >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.
> >> >>
> >> >>
> >>
> >>

>
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      19th Jun 2009
Yes, the subquery needs to be in parentheses, so you need to add a closing
bracket to get:

([PropNum] Like "55*")
AND ([StreetName] Like "Mead*")
AND ([Archive] = False)
AND 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] = "Public"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Carol2901" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> Hi Allen,
>
> Here is the debug.print -
> ([PropNum] Like "55*") AND ([StreetName] Like "Mead*") AND ([Archive] =
> False) AND 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] = "Public")
>
> and here is the code -
> If Not IsNull(Me.ClientLast) Then
> strWhere = 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
>
> I do notice that there is a parenthesis in before SELECT but I don't find
> an
> ending parenthesis. I have tried inserting one in what seem obvious places
> to
> me but to no avail. I have never seen the term NOT EXISTS before and
> haven't
> had time to research it but am assuming it means to look for a table other
> than the current Record Source?
>
> Thanks again.
> Carol
>
> "Allen Browne" wrote:
>
>> 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
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>> "Carol2901" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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/Ap****ueBadWord.html
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Carol2901" <(E-Mail Removed)> wrote in message
>> >> news:(E-Mail Removed)...
>> >> > 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.
>> >> >
>> >> > "Allen Browne" wrote:
>> >> >
>> >> >> 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
>> >> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >> >>
>> >> >> "Carol2901" <(E-Mail Removed)> wrote in message
>> >> >> news:(E-Mail Removed)...
>> >> >> >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.
>> >> >>
>> >> >>
>> >>
>> >>

>>
>>


 
Reply With Quote
 
Carol2901
Guest
Posts: n/a
 
      19th Jun 2009
Just wanted to let you know that after adding the ending parenthesis that I
finally got some results; however it was all 5,000+ records. So, because I
don't understand 'Not Exists' and couldn't find a reference to it in the help
file, I removed it from the clause. and got perfect results. I intend to
continue testing with runtime before putting it out for all 500 agents and if
you know of any problems which might arise, I would appreciate hearing of
them.

Thanks again for your help. The few employees/agents using/testing the basic
search are most impressed with how much quicker they are able to get results
compared to the original version which used multiple queries, forms and
reports. This will be much easier to maintain.

My next challenge is along the same lines, but involves joining 2 additional
tables to search by agent name and I will probably start a new post next week
when I go back to work.

Carol

"Allen Browne" wrote:

> Yes, the subquery needs to be in parentheses, so you need to add a closing
> bracket to get:
>
> ([PropNum] Like "55*")
> AND ([StreetName] Like "Mead*")
> AND ([Archive] = False)
> AND 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] = "Public"))
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Carol2901" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> > Hi Allen,
> >
> > Here is the debug.print -
> > ([PropNum] Like "55*") AND ([StreetName] Like "Mead*") AND ([Archive] =
> > False) AND 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] = "Public")
> >
> > and here is the code -
> > If Not IsNull(Me.ClientLast) Then
> > strWhere = 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
> >
> > I do notice that there is a parenthesis in before SELECT but I don't find
> > an
> > ending parenthesis. I have tried inserting one in what seem obvious places
> > to
> > me but to no avail. I have never seen the term NOT EXISTS before and
> > haven't
> > had time to research it but am assuming it means to look for a table other
> > than the current Record Source?
> >
> > Thanks again.
> > Carol
> >
> > "Allen Browne" wrote:
> >
> >> 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
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> 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/Ap****ueBadWord.html
> >> >>
> >> >> --
> >> >> Allen Browne - Microsoft MVP. Perth, Western Australia
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> >> news:(E-Mail Removed)...
> >> >> > 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.
> >> >> >
> >> >> > "Allen Browne" wrote:
> >> >> >
> >> >> >> 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
> >> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >> >>
> >> >> >> "Carol2901" <(E-Mail Removed)> wrote in message
> >> >> >> news:(E-Mail Removed)...
> >> >> >> >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.
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>

>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Some help on converting some code from Allen Browne search2000 kostas Microsoft Access VBA Modules 1 18th Sep 2008 11:41 AM
Anyone using David Allen's "Getting Things Done in Outlook" add-in or the guide book I live on Quicken and Outlook Microsoft Outlook 0 2nd Feb 2005 07:54 AM
Explorer and fake tool bar from search2000.com =?Utf-8?B?Um9u?= Windows XP Security 1 24th Aug 2004 02:31 AM
search2000.com dave123 Windows XP Internet Explorer 1 11th Jun 2004 01:53 AM
for Jack (or Len or Allen) anonymous@discussions.microsoft.com Windows XP Help 13 11th Mar 2004 10:10 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:59 AM.