Allen Browne's Search2000

Discussion in 'Microsoft Access Form Coding' started by Carol2901, Jun 16, 2009.

  1. Carol2901

    Carol2901 Guest

    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.
     
    Carol2901, Jun 16, 2009
    #1
    1. Advertisements

  2. Carol2901

    Allen Browne Guest

    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" <> wrote in message
    news:...
    >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.
     
    Allen Browne, Jun 16, 2009
    #2
    1. Advertisements

  3. Carol2901

    Carol2901 Guest

    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" <> wrote in message
    > news:...
    > >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.

    >
    >
     
    Carol2901, Jun 17, 2009
    #3
  4. Carol2901

    Allen Browne Guest

    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
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Carol2901" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> >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.

    >>
    >>
     
    Allen Browne, Jun 18, 2009
    #4
  5. Carol2901

    Carol2901 Guest

    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/AppIssueBadWord.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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > >> news:...
    > >> >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.
    > >>
    > >>

    >
    >
     
    Carol2901, Jun 18, 2009
    #5
  6. Carol2901

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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/AppIssueBadWord.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" <> wrote in message
    >> news:...
    >> > 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" <> wrote in message
    >> >> news:...
    >> >> >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.
    >> >>
    >> >>

    >>
    >>
     
    Allen Browne, Jun 19, 2009
    #6
  7. Carol2901

    Carol2901 Guest

    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" <> wrote in message
    > news:...
    > > 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/AppIssueBadWord.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" <> wrote in message
    > >> news:...
    > >> > 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" <> wrote in message
    > >> >> news:...
    > >> >> >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.
    > >> >>
    > >> >>
    > >>
    > >>

    >
    >
     
    Carol2901, Jun 19, 2009
    #7
  8. Carol2901

    Allen Browne Guest

    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" <> wrote in message
    news:D...
    > 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" <> wrote in message
    >> news:...
    >> > 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/AppIssueBadWord.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" <> wrote in message
    >> >> news:...
    >> >> > 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" <> wrote in message
    >> >> >> news:...
    >> >> >> >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.
    >> >> >>
    >> >> >>
    >> >>
    >> >>

    >>
    >>
     
    Allen Browne, Jun 19, 2009
    #8
  9. Carol2901

    Carol2901 Guest

    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" <> wrote in message
    > news:D...
    > > 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" <> wrote in message
    > >> news:...
    > >> > 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/AppIssueBadWord.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" <> wrote in message
    > >> >> news:...
    > >> >> > 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" <> wrote in message
    > >> >> >> news:...
    > >> >> >> >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.
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >>
    > >>

    >
    >
     
    Carol2901, Jun 19, 2009
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. A Hopper

    Allen Browne - Rounding Up and Down

    A Hopper, Oct 6, 2003, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    191
    A Hopper
    Oct 7, 2003
  2. Tony Girgenti

    Allen Browne's "Limiting a Report to a Date Range"

    Tony Girgenti, Oct 18, 2003, in forum: Microsoft Access Form Coding
    Replies:
    12
    Views:
    311
    Tony Girgenti
    Oct 22, 2003
  3. findasutype By Allen Brownes

    , Feb 13, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    207
    ruralguy via AccessMonster.com
    Feb 13, 2008
  4. Asib

    Allen Brownes ECount()

    Asib, Sep 9, 2009, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    242
  5. Barry A&P

    help with allen brownes Lock bound controls

    Barry A&P, Mar 4, 2010, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    654
    Barry A&P
    Mar 5, 2010
Loading...

Share This Page