Runtime error 2001, query In Statement

[RECrerar]

Hi,

I can't for the life of me see what is wrong with my code as to my mind it is the same as another chunk of code i have that works so thought I would put it here and hope someone can point out what is going on.

I am trying to filter records on a subform through 2 multi select list boxes. (actually there are more than two, but for the purpose of this post the rest can be ignored)

The list boxes are both 2 columns pouplated from indevidual simple tables, the first (bound column) containing the item ID and the second the info to display. The field in the subform are comboboxes which are puopulated from the same tables that populate the listboxes.

The code I am using is as follows.

The section below works and gives the recod source statement beow when only one option is selected:

SELECT * FROM qryFiltered WHERE [Business_Sector] IN ("EO");

strIn = ""
If Me.lsBusArea.ItemsSelected.count > 0 Then
    For Each iSelected In Me.lsBusArea.ItemsSelected
        strIn = strIn & """" & Me.lsBusArea.Column(1, iSelected) & """, "
    Next iSelected
 
    lngLen = Len(strIn) - 2 'Without trailing comma and space.
    strWhere = strWhere & "[Business_Sector] IN (" & Left$(strIn, lngLen) & ") AND "
End If
strWhere = Left(strWhere, Len(strWhere) - 5) ' & ")"
 
Me![sfrmFlexibleComparisms].Form.RecordSource = "SELECT * FROM qryFiltered WHERE  " & strWhere & ";"

The section below now, does not work, giving me a runtime error 2001

strIn = ""
If Me.lsStatus.ItemsSelected.count > 0 Then
    For Each iSelected In Me.lsStatus.ItemsSelected
        strIn = strIn & """" & Me.lsStatus.Column(1, iSelected) & """, "
    Next iSelected
 
    lngLen = Len(strIn) - 2 'Without trailing comma and space.
    strWhere = strWhere & "[Status] IN (" & Left$(strIn, lngLen) & ") AND "
End If
  strWhere = Left(strWhere, Len(strWhere) - 5) ' & ")"
  Me![sfrmFlexibleComparisms].Form.RecordSource = "SELECT * FROM qryFiltered WHERE  " & strWhere & ";"

It gives the record source statement below when only one option is selected

SELECT * FROM qryFiltered WHERE [Status] IN ("Prospect");

I can't see how this latter statement is any different to the one above that functions perfectly.

What I have done/tried:

I checked that the format of the listboxes on the main form and the combo boxes on the subform are the same, same cound column etc.

Checked all my spelling

Compact and Repair

modified

strIn = strIn & """" & Me.lsStatus.Column(1, iSelected) & """, "

to

strIn = strIn & """" & Me.lsStatus.itemdata(iSelected) & """, "

to return the ID numbers in the string instead of the text

SELECT * FROM qryFiltered WHERE [Status] IN ("1");

Checked the data type of the underlying fields.

I can't for the life of me see why one works perfectally and the other keeps having fits. If anyone could suggest anything else I may have missed it would be greatly appreciated

Obviously using the IN statement is a bit redundant when only a single option is selected, however I went this route as it was suggested as working well for multipule selections in another post I was viewing.

 

[RECrerar]

Hi,

I've found out what the difference was. The relationships between the main table (that was in the sub form) and the listbox/combobox tables. One was linked on the ID and the other on the description field.

Now here's a question.

If I am only using a table as a list of lookup values does is it good for it to have an ID field? Mine currently do but as shown above it is making this difficult. Is there any advantage to it being there or could I just get rid of it?

 

[RECrerar]

Hey again,

I know I'm talking to myself but I have another lookup. I've been reading that lookup fields in tables are a very bad idea and can cause no end of issues.

This is a pain as I have loads of these and all the fields are used elsewhere on forms and reports and so forth and so forth and just generally aaargh. But by reading the posts it seems that it will be worth the effort to change these to linked fields so I am giong to start a major redesign and clear all of this out and hopefully that will clear up some of the issues.

I will be back to the question in this thread but probably not for a while until I have everythign cleared.

Regards
Robyn