Problem with IN Operator

[Jon Lewis]

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

 

[Sprinks]

Jon,

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

Sprinks

 

[Jon Lewis]

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.

Jon,

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

Sprinks

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

 

[Jon Lewis]

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.

Jon,

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

Sprinks

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

 

[Sprinks]

Glad you got it resolved, Jon.

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.

Jon,

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

Sprinks

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