query to satisfy condition in multiple tables confusion....

[markjkubicki]

I think I'm totally lost...

I have a code that creates a query filtered on the values of one table, and it's fine (works exactly as I wanted);
then I needed to expand it to include records in a second related table (that's where I got all twisted)

the GOAL IS:
to show all the records in table [tblManufacturer] where either
in that table [tblManufacturer], the field [PrimaryCatagoy] meets the criteria in [strIN]
or
in a second table [tblMfrCatagory] (related to [tblManufacturer] by the field [ManufacturerName]), the field [AddlCatagories] meets the same criteria


' this alone executed correctly:


' strSQL = strSQL & "WHERE tblManufacturers.PrimaryCatagory IN(" & Left(strIN, Len(strIN) - 1) & ")"

'this does not execute at all:
strSQL = strSQL & "WHERE (tblManufacturers.PrimaryCatagory IN(" & Left(strIN, Len(strIN) - 1) & "))" _
or _
(tblMfrCatagory.AddlCatagories IN(" & Left(strIN, Len(strIN) - 1) & "));"
End If

'Clear listbox selection after running query"
For Each varItem In Me.lstCatagorySelect.ItemsSelected
Me.lstCatagorySelect.Selected(varItem) = False
Next varItem
Me.Label27.Caption = "Currently filtered to include: " & strLabel
Me.Label27.Visible = True

Forms!frmManufactures.RecordSource = strSQL
MyDB.QueryDefs.Delete "qryCatagories"
Set qdef = MyDB.CreateQueryDef("qryCatagories", strSQL)



greatly appreciated in advance,
Mark

[June7]

How do you build the strIN criteria? The IN operation won't work if strIN is just a single string of characters, not a set of discrete values. Don't see how it could have worked correctly at all.

In("France", "China", "Germany", "Japan") is not the same as In("France, China, Germany, Japan")

The first is easy to do in Access query object with literal text, not so easy to build in VBA. I've tried.

[markjkubicki]

' Build string by looping through the listbox
strIN = ""
For i = 0 To lstCatagorySelect.ListCount - 1
If lstCatagorySelect.Selected(i) Then
strIN = strIN & "'" & lstCatagorySelect.Column(0, i) & "', "
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string

[June7]

Ah Ha! Had to use DAO recordset. Wow, works, now I should never forget.

Need to trim a comma and space so:

Left(strIN, Len(strIN) - 2)

[markjkubicki]

Not sure that will get me where I'm trying to go (out of office and on cell at the moment, and unable to run the code.... )
If I recall correctly, the problem gets flagged while I'm writing the code at the second "IN"
....tblMfrCatagory.addlCatagories IN("

Could I bother you to post the entire line as you see it ought to be written?

[June7]

After looping through the listbox:

strSQL = strSQL & "WHERE tblManufacturers.PrimaryCatagory IN(" & Left(strIN, Len(strIN) - 2) & ")" _

Do you really have the field named as PrimaryCatagory? That is a misspelling of Category.

I assume the strSQL has a space built in to precede the "WHERE..." and a mate to the last paren.

[markjkubicki]

Yup
Catagory
Things you inherit...