Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!   Become a Supporter    ::   Make a Contribution   
    Monthly (Recurring) Target: $300 :: Achieved: $95

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
    > >>
    > >>
    > >>
    > >>

    >
    >
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page