report filter code

[Carma via AccessMonster.com]

Hi,
I have a report which uses the attached code to filter the report by items
selected in a MultiSelect listbox.

But when i run this code I have the following problems:

When I run this code I get the following error when I select one item from
the
list: Run-time error '3075'. Syntax error in string in query expression '
(PositionExclusion in ('P))'.

If I select two items i get: Run-time error '3075'. Syntax error in string in
query expression '(PositionExclusion in ('P','Y))'.

Could someone please help me with the syntax. Much appreciated!

Private Sub Command103_Click()

Dim strTemp As String
Dim strWhere As String
Dim varSelected As Variant

If Me.chkExcl = True Then
If Me.lstPosExcl.ItemsSelected.Count > 0 Then
strTemp = vbNullString
For Each varSelected In Me.lstPosExcl.ItemsSelected
strTemp = strTemp & "'" & Me.lstPosExcl.ItemData(varSelected) & " ',"
Next varSelected
strWhere = strWhere & _
"PositionExclusion IN (" & Left(strTemp, Len(strTemp) - 2) & ") AND "
End If
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

DoCmd.OpenReport "RptListUniversaltest", acPreview, , strWhere


Thanks!

 

[ND Pard]

Try this:

Place the statement:

MsgBox strWhere

on the line immediately above your DoCmd.OpenReport ... statement.

I think you'll figure it out from there.

Good Luck.

 

[Carma via AccessMonster.com]

Hi,

Thanks for your response. I input you change and when i run it I get the
message... Position Exclusion in ('P','Y) ... Now I know your really trying
to show/teach me how to identify the problem myself but unfortunately I'm VBA
stupid and don't know what the problem is. I suspect that I'm missing a ' or
" somewhere but like I said I'm code stupid and don't know where.

I would surely appreciate your help again as I suspect you know exactly what
the error is.

Thank you so much! I have to learn VBA soon.

Try this:

Place the statement:

MsgBox strWhere

on the line immediately above your DoCmd.OpenReport ... statement.

I think you'll figure it out from there.

Good Luck.

Hi,
I have a report which uses the attached code to filter the report by items

[quoted text clipped - 36 lines]

Thanks!

 

[ND Pard]

You already answered the problem ...

you ARE missing the apostrophe after the " 'Y ".

It should look like ('P','Y').

Good Luck.

Hi,

Thanks for your response. I input you change and when i run it I get the
message... Position Exclusion in ('P','Y) ... Now I know your really trying
to show/teach me how to identify the problem myself but unfortunately I'm VBA
stupid and don't know what the problem is. I suspect that I'm missing a ' or
" somewhere but like I said I'm code stupid and don't know where.

I would surely appreciate your help again as I suspect you know exactly what
the error is.

Thank you so much! I have to learn VBA soon.

Try this:

Place the statement:

MsgBox strWhere

on the line immediately above your DoCmd.OpenReport ... statement.

I think you'll figure it out from there.

Good Luck.

Hi,
I have a report which uses the attached code to filter the report by items

[quoted text clipped - 36 lines]

Thanks!

 

[Paolo]

Hi Carma,
I think that you have to subtract just 1 to the lenght of strtemp in this way

strWhere = strWhere & _
"PositionExclusion IN (" & Left(strTemp, Len(strTemp) - 1) & ") AND "

If you subtract 2 you'll clip the the apostrophe that close the string and
this is what raise the error.

HTH Paolo

 

[Carma via AccessMonster.com]

Wooohooo it worked. Thanks a million Paolo!

Not that you will ever see it but I will definately add a comment in my code
to thank you, NN Pard and others who helped me with this.

Do you have a suggestion as to the best way to learn VBA... internet, book,
course???

Hi Carma,
I think that you have to subtract just 1 to the lenght of strtemp in this way

strWhere = strWhere & _
"PositionExclusion IN (" & Left(strTemp, Len(strTemp) - 1) & ") AND "

If you subtract 2 you'll clip the the apostrophe that close the string and
this is what raise the error.

HTH Paolo

Hi,
I have a report which uses the attached code to filter the report by items

[quoted text clipped - 36 lines]

Thanks!

 

[Paolo]

I'm happy my suggestion solved your problem and thank you for the comment
you'll add in your code!!
I'm sorry, I've no suggestion on how to learn VBA.

Cheers Paolo

Wooohooo it worked. Thanks a million Paolo!

Not that you will ever see it but I will definately add a comment in my code
to thank you, NN Pard and others who helped me with this.

Do you have a suggestion as to the best way to learn VBA... internet, book,
course???

Hi Carma,
I think that you have to subtract just 1 to the lenght of strtemp in this way

strWhere = strWhere & _
"PositionExclusion IN (" & Left(strTemp, Len(strTemp) - 1) & ") AND "

If you subtract 2 you'll clip the the apostrophe that close the string and
this is what raise the error.

HTH Paolo

Hi,
I have a report which uses the attached code to filter the report by items

[quoted text clipped - 36 lines]

Thanks!