Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Problem with IN Operator

Discussion in 'Information Technology' started by Jon Lewis, Nov 11, 2005.

  1. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    I've have the following clause to apply as a form Filter in VBA:

    strFilter = "[CompanyID] IN (SELECT [CompanyID] From qryCompaniesCategories
    WHERE [CategoryName] IN ( " & strCategories & "))"

    strCategories is constructed in code correctly (as far as I'm aware) so if
    strFilter is examined when debugging it would read as an example:

    strFilter = [CompanyID] IN (SELECT [CompanyID] From qryCompaniesCategories
    WHERE [CategoryName] IN ( 'Name1', 'Name2', 'Name3'))

    The problem is this only ever returns Name1 companies (or whatever the first
    item in the list is). qryCompaniesCategories is a well used query so has
    nothing to do with the problem.

    Have been wrestling with this for ages (tried with & without space after the
    comma separating the 'Name's BTW).

    TIA
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikhs Face Problems While Carrying Kirpans In Khyber-Pakhtunkhwa - SikhSiyasat.Net Breaking News Jul 29, 2016
    "Dasam" Granth - A Look At The Core Problems Dasam Granth Oct 21, 2015
    Problems In Life Sikh Sikhi Sikhism Apr 13, 2015
    How can I make decent and useful friends and from where ?? most people have a problem with me Sikh Youth Oct 23, 2013
    The Problem with Taking Too Many Vitamins Health & Nutrition Oct 18, 2013

  3. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Jon,

    I suspect the problem is in the assignment of strCategories. Please post
    this code.

    Sprinks

    "Jon Lewis" wrote:

    > I've have the following clause to apply as a form Filter in VBA:
    >
    > strFilter = "[CompanyID] IN (SELECT [CompanyID] From qryCompaniesCategories
    > WHERE [CategoryName] IN ( " & strCategories & "))"
    >
    > strCategories is constructed in code correctly (as far as I'm aware) so if
    > strFilter is examined when debugging it would read as an example:
    >
    > strFilter = [CompanyID] IN (SELECT [CompanyID] From qryCompaniesCategories
    > WHERE [CategoryName] IN ( 'Name1', 'Name2', 'Name3'))
    >
    > The problem is this only ever returns Name1 companies (or whatever the first
    > item in the list is). qryCompaniesCategories is a well used query so has
    > nothing to do with the problem.
    >
    > Have been wrestling with this for ages (tried with & without space after the
    > comma separating the 'Name's BTW).
    >
    > TIA
    >
    >
    >
    >
     
  4. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    Dim strCategories As String
    strCategories = "'" & Me.txtCategories & "'"

    If InStr(strCategories, " OR ") <> 0 Then
    While InStr(strCategories, " OR ") <> 0
    strCategories = Left(strCategories, InStr(strCategories, " OR ") -
    1) & "', '" & Mid(strCategories, InStr(strCategories, " OR ") + 3)
    Wend
    strFilter = "[CompanyID] IN (SELECT [CompanyID] From
    qryCompaniesCategories WHERE [CategoryName] IN (" & strCategories & "))"
    End If

    txtCategories is a text box populated by a process of choosing categories.
    It could also contain an 'AND' separated list hence the parsing process.

    I think the construct of strCategories is OK (but I might be wrong!!).


    Thanks.

















    "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    news:A19D681C-B5C7-4068-884E-7AACE0BCB2B8@microsoft.com...
    > Jon,
    >
    > I suspect the problem is in the assignment of strCategories. Please post
    > this code.
    >
    > Sprinks
    >
    > "Jon Lewis" wrote:
    >
    >> I've have the following clause to apply as a form Filter in VBA:
    >>
    >> strFilter = "[CompanyID] IN (SELECT [CompanyID] From
    >> qryCompaniesCategories
    >> WHERE [CategoryName] IN ( " & strCategories & "))"
    >>
    >> strCategories is constructed in code correctly (as far as I'm aware) so
    >> if
    >> strFilter is examined when debugging it would read as an example:
    >>
    >> strFilter = [CompanyID] IN (SELECT [CompanyID] From
    >> qryCompaniesCategories
    >> WHERE [CategoryName] IN ( 'Name1', 'Name2', 'Name3'))
    >>
    >> The problem is this only ever returns Name1 companies (or whatever the
    >> first
    >> item in the list is). qryCompaniesCategories is a well used query so has
    >> nothing to do with the problem.
    >>
    >> Have been wrestling with this for ages (tried with & without space after
    >> the
    >> comma separating the 'Name's BTW).
    >>
    >> TIA
    >>
    >>
    >>
    >>
     
  5. Jon Lewis

    Jon Lewis
    Expand Collapse
    Guest

    Re: Problem with IN Operator(Solved!)

    Hands up, You're right!

    & Mid(strCategories, InStr(strCategories, " OR ") + 3)
    was putting a leading zero on the category name.
    & Mid(strCategories, InStr(strCategories, " OR ") + 4) works fine.

    Thanks anyway.

    "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    news:A19D681C-B5C7-4068-884E-7AACE0BCB2B8@microsoft.com...
    > Jon,
    >
    > I suspect the problem is in the assignment of strCategories. Please post
    > this code.
    >
    > Sprinks
    >
    > "Jon Lewis" wrote:
    >
    >> I've have the following clause to apply as a form Filter in VBA:
    >>
    >> strFilter = "[CompanyID] IN (SELECT [CompanyID] From
    >> qryCompaniesCategories
    >> WHERE [CategoryName] IN ( " & strCategories & "))"
    >>
    >> strCategories is constructed in code correctly (as far as I'm aware) so
    >> if
    >> strFilter is examined when debugging it would read as an example:
    >>
    >> strFilter = [CompanyID] IN (SELECT [CompanyID] From
    >> qryCompaniesCategories
    >> WHERE [CategoryName] IN ( 'Name1', 'Name2', 'Name3'))
    >>
    >> The problem is this only ever returns Name1 companies (or whatever the
    >> first
    >> item in the list is). qryCompaniesCategories is a well used query so has
    >> nothing to do with the problem.
    >>
    >> Have been wrestling with this for ages (tried with & without space after
    >> the
    >> comma separating the 'Name's BTW).
    >>
    >> TIA
    >>
    >>
    >>
    >>
     
  6. Sprinks

    Sprinks
    Expand Collapse
    Guest

    Re: Problem with IN Operator(Solved!)

    Glad you got it resolved, Jon.

    "Jon Lewis" wrote:

    > Hands up, You're right!
    >
    > & Mid(strCategories, InStr(strCategories, " OR ") + 3)
    > was putting a leading zero on the category name.
    > & Mid(strCategories, InStr(strCategories, " OR ") + 4) works fine.
    >
    > Thanks anyway.
    >
    > "Sprinks" <Sprinks@discussions.microsoft.com> wrote in message
    > news:A19D681C-B5C7-4068-884E-7AACE0BCB2B8@microsoft.com...
    > > Jon,
    > >
    > > I suspect the problem is in the assignment of strCategories. Please post
    > > this code.
    > >
    > > Sprinks
    > >
    > > "Jon Lewis" wrote:
    > >
    > >> I've have the following clause to apply as a form Filter in VBA:
    > >>
    > >> strFilter = "[CompanyID] IN (SELECT [CompanyID] From
    > >> qryCompaniesCategories
    > >> WHERE [CategoryName] IN ( " & strCategories & "))"
    > >>
    > >> strCategories is constructed in code correctly (as far as I'm aware) so
    > >> if
    > >> strFilter is examined when debugging it would read as an example:
    > >>
    > >> strFilter = [CompanyID] IN (SELECT [CompanyID] From
    > >> qryCompaniesCategories
    > >> WHERE [CategoryName] IN ( 'Name1', 'Name2', 'Name3'))
    > >>
    > >> The problem is this only ever returns Name1 companies (or whatever the
    > >> first
    > >> item in the list is). qryCompaniesCategories is a well used query so has
    > >> nothing to do with the problem.
    > >>
    > >> Have been wrestling with this for ages (tried with & without space after
    > >> the
    > >> comma separating the 'Name's BTW).
    > >>
    > >> TIA
    > >>
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page