2 Multiple List Boxes, Now getting Error 3075

[Gabby Girl]

Good Morning,

I have two multiple select list boxes that are used to filter a form. When
I try them seperately they work fine (comment out code for one & test the
other). But as soon as I try to make them both work together I get: Error
3075. Syntax Error (missing operator) in query expression '[ModelNbr] IN
([ManufacturesMake] IN ("Ford")"F150","F250")'.

I'm using code from Allen Browne (Use a Multi-select list box to filter a
report) and then adapted (obviously not very well) to my situation. I'm
assuming it's a missing bracket, but can't for the life of me figure out
where in the code it's missing. Could someone please look at my code a
enlighten me as to what I'm missing. (I've been working on this for 3 days
and have gone from blonde to bald trying to figure this out.

Code:
************************************

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "frmCARReportResults"

'Loop through the ItemsSelected in the list box.
With Me.lstManufacturesMake
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ManufacturesMake] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ManufacturesMake: " &
Left$(strDescrip, lngLen)
End If


With Me.lstModelNbr
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim &
","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ModelNbr] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ModelNbr: " & Left$(strDescrip,
lngLen)
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
'If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
'End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End If

End Sub


**************************

I appreciate any help that anyone can give me.

Thanks Kindly

 

[Jerry Porter]

Gabby,

You'll need to integrate the sections for the 2 listboxes better. The
variable strWhere is being used to create the filter for your form. You
need to modify the code so the conditions for the 2 listboxes are
appended with "AND" between them. Right now, the 2nd section is kind of
swallowing the result of the 1st section.

The result you want to end up with in strWhere should be something
like:

[ManufacturesMake] IN ("Ford") And [ModelNbr] IN ("F150","F250")

You can see (in your error message) that it's gotten tangled up.

It might help to create separate variables strWhereMake and
strWhereModel to use in each section. Then after both sections (before
opening the form), combine them with

strWhere = strWhereMake & " And " & strWhereModel

It doesn't look like the variable strDescrip actually gets used for
anything, but it might have a similar problem.

Jerry

Good Morning,

I have two multiple select list boxes that are used to filter a form. When
I try them seperately they work fine (comment out code for one & test the
other). But as soon as I try to make them both work together I get: Error
3075. Syntax Error (missing operator) in query expression '[ModelNbr] IN
([ManufacturesMake] IN ("Ford")"F150","F250")'.

I'm using code from Allen Browne (Use a Multi-select list box to filter a
report) and then adapted (obviously not very well) to my situation. I'm
assuming it's a missing bracket, but can't for the life of me figure out
where in the code it's missing. Could someone please look at my code a
enlighten me as to what I'm missing. (I've been working on this for 3 days
and have gone from blonde to bald trying to figure this out.

Code:
************************************

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "frmCARReportResults"

'Loop through the ItemsSelected in the list box.
With Me.lstManufacturesMake
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ManufacturesMake] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ManufacturesMake: " &
Left$(strDescrip, lngLen)
End If


With Me.lstModelNbr
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim &
","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ModelNbr] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ModelNbr: " & Left$(strDescrip,
lngLen)
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
'If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
'End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End If

End Sub


**************************

I appreciate any help that anyone can give me.

Thanks Kindly

 

[Gabby Girl]

Hi Jerry,

Thanks so much for taking the time to help me. I took your suggestion and
created seperate variables and now the Error message I'm getting is: Error
3075. Syntax Error (missing operator) in query expression ' And '.

I also tried putting "AND" between the 2 listboxes conditions but then I
got an Error 13 - Type mismatch. I'm obviously very confused as to where the
"AND" should go. Should it go right before I state With Me.lstModelNbr ? or
where ?

I apologize for my ignorance but could you please bear with me?

Thanks Kindly

Gabby,

You'll need to integrate the sections for the 2 listboxes better. The
variable strWhere is being used to create the filter for your form. You
need to modify the code so the conditions for the 2 listboxes are
appended with "AND" between them. Right now, the 2nd section is kind of
swallowing the result of the 1st section.

The result you want to end up with in strWhere should be something
like:

[ManufacturesMake] IN ("Ford") And [ModelNbr] IN ("F150","F250")

You can see (in your error message) that it's gotten tangled up.

It might help to create separate variables strWhereMake and
strWhereModel to use in each section. Then after both sections (before
opening the form), combine them with

strWhere = strWhereMake & " And " & strWhereModel

It doesn't look like the variable strDescrip actually gets used for
anything, but it might have a similar problem.

Jerry

Good Morning,

I have two multiple select list boxes that are used to filter a form. When
I try them seperately they work fine (comment out code for one & test the
other). But as soon as I try to make them both work together I get: Error
3075. Syntax Error (missing operator) in query expression '[ModelNbr] IN
([ManufacturesMake] IN ("Ford")"F150","F250")'.

I'm using code from Allen Browne (Use a Multi-select list box to filter a
report) and then adapted (obviously not very well) to my situation. I'm
assuming it's a missing bracket, but can't for the life of me figure out
where in the code it's missing. Could someone please look at my code a
enlighten me as to what I'm missing. (I've been working on this for 3 days
and have gone from blonde to bald trying to figure this out.

Code:
************************************

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "frmCARReportResults"

'Loop through the ItemsSelected in the list box.
With Me.lstManufacturesMake
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ManufacturesMake] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ManufacturesMake: " &
Left$(strDescrip, lngLen)
End If


With Me.lstModelNbr
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim &
","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ModelNbr] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "tblEquipmentMaster.ModelNbr: " & Left$(strDescrip,
lngLen)
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
'If CurrentProject.AllReports(strDoc).IsLoaded Then
'DoCmd.Close acReport, strDoc
'End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenForm strDoc, acNormal, WhereCondition:=strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End If

End Sub


**************************

I appreciate any help that anyone can give me.

Thanks Kindly

 

[Jerry Porter]

The 'And' will be part of a text string that represents a query where
condition. It looks like you've somehow gotten your where string to be
just ' And ' and nothing else.

I can't say for sure without seeing the current procedure, but here's a
possible explanation. If you used the line I suggested:
strWhere = strWhereMake & " And " & strWhereModel
then strWhereMake and strWhereModel are apparently blank, so the result
just has " And ". After creating these two variables, you need to use
them to build the 2 parts of your where string. In your two sections of
code for the listboxes, you need to use these variables instead of
strWhere.

strWhere = strWhere & strDelim ...
would change to
strWhereMake = strWhereMake & strDelim ...
in the first section, and
strWhereModel = strWhereModel & strDelim ...
in the 2nd section

strWhere should not be used until the line
strWhere = strWhereMake & " And " & strWhereModel

It would help you in general to learn to understand what each line of
the code does. Then use debugging tools (set break points and use the
immediate window or watch points) to examine the values of variables to
make sure the code is doing what you expect/need. Access has a Help
topic titled "Debug your Visual Basic code".

Jerry

 

[Gabby Girl]

Hello again Jerry,

I really appreciate you getting back to me and I apologize for the delay in
responding.

Thank you very very much. It's now working. I had changed my two list
conditions as you had said previously, but I put my strWhere in the wrong
place and I hadn't put spaces between & " And " &. I also looked up and
applied the debugging tools as you suggested and of course that made things
quite a bit clearer.

So again Thank you very much and I really do appreciate all of your help.

 

[Jerry Porter]

You're welcome! Good job getting it to work.

Jerry