Multiple Combo Boxes as well as multiple Multi select List Boxes toreport

[micarl@gmail.com]

How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?

I can get one Multi List Box, just not several, to report using this
code i found -


Private Sub cmdPreview_Clic k()

On Error GoTo Err_Handler

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 = "Products by Category"



With Me.lstCategory

For Each varItem In .ItemsSelected

If Not IsNull(varItem) Then


strWhere = strWhere & strDelim & .ItemData(varIt em) &
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



lngLen = Len(strWhere) - 1

If lngLen 0 Then

strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"

lngLen = Len(strDescrip) - 2

If lngLen 0 Then

strDescrip = "Categories : " & Left$(strDescri p, lngLen)

End If

End If



If CurrentProject. AllReports(strD oc).IsLoaded Then

DoCmd.Close acReport, strDoc

End If



DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip



Exit_Handler:

Exit Sub



Err_Handler:

If Err.Number <2501 Then 'Ignore "Report cancelled" error.

MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"

End If

Resume Exit_Handler

End Sub



Any help would be greatly apprciated!

Many thanks

Michael

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

No one can help me?

[Salad]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

micarl@gmail.co m wrote:

How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?
>
I can get one Multi List Box, just not several, to report using this
code i found -
>
>
Private Sub cmdPreview_Clic k()
>
On Error GoTo Err_Handler
>
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 = "Products by Category"
>
>
>
With Me.lstCategory
>
For Each varItem In .ItemsSelected
>
If Not IsNull(varItem) Then
>
>
strWhere = strWhere & strDelim & .ItemData(varIt em) &
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
>
>
>
lngLen = Len(strWhere) - 1
>
If lngLen 0 Then
>
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
>
lngLen = Len(strDescrip) - 2
>
If lngLen 0 Then
>
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
>
End If
>
End If
>
>
>
If CurrentProject. AllReports(strD oc).IsLoaded Then
>
DoCmd.Close acReport, strDoc
>
End If
>
>
>
DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip
>
>
>
Exit_Handler:
>
Exit Sub
>
>
>
Err_Handler:
>
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
>
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
>
End If
>
Resume Exit_Handler
>
End Sub
>
>
>
Any help would be greatly apprciated!
>
Many thanks
>
Michael

You have a good start. Let's say you have 3 listboxes; List0, List1,
List2.

Dim strList0 As String
Dim strList1 As String
Dim strList2 As String
Dim strWhere As STring

Change strWhere in the code for the first listbox to strList0. Ex:
strWhere = strWhere & strDelim & .ItemData(varIt em) &
to
strList0 = strList0 & strDelim & .ItemData(varIt em) &

Now copy the code for scanning lstCategory selections then paste it
before the line
If CurrentProject. AllReports(strD oc).IsLoaded Then

Change that code to reflect the data from that listbox and assign it to
strList1

Keep copying/pasting and updating the code for the listbox until all
listbox selections are complete. Then do something like
If strList0 "" then strWhere = strWhere & strList0 & " And "
If strList1 "" then strWhere = strWhere & strList1 & " And "
If strList2 "" then strWhere = strWhere & strList2 & " And "
strWhere = Left(strWhere,l en(strWhere)-5) 'remove trailing And

For debugging purposes I might put a line like
Debug.print strWhere
so you can view the final result

Then call the report.

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

Thanks for the reply. I don't know how this code would fit in.

If i had one Combo Box called CountrySelect and another called
GroupSelect to print to a report called rptContact what code would i
use?
The button i have set up to print the report uses the selected fields
to filter data, i currently have -

Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptContact "

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

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ContactID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If

If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
End Sub

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

I've tried following your instructions but this code doesn't work -

Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strCountrySelec t As String
Dim strCategorySele ct As String
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDoc = "rptContact "

With Me.CountrySelec t
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelec t = strCountrySelec t & strDelim
& .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[CountryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If

lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If

If strCountrySelec t "" Then strWhere = strWhere &
strCountrySelec t & " And "
If strCategorySele ct "" Then strWhere = strWhere &
strCategorySele ct & " And "
strWhere = Left(strWhere, Len(strWhere) - 2)

If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
End Sub

[Salad]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

micarl@gmail.co m wrote:

Thanks for the reply. I don't know how this code would fit in.
>
If i had one Combo Box called CountrySelect and another called
GroupSelect to print to a report called rptContact what code would i
use?
The button i have set up to print the report uses the selected fields
to filter data, i currently have -
>
Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler
>
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
>
strDoc = "rptContact "
>
With Me.CountrySelec t
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varIt em) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With
>
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "[ContactID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If
>
If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
>
DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip
>
Exit_Handler:
Exit Sub
>
Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
End Sub

What is strDelim set to?

If using IN, a string should be surrounded in ' or ". Ex
Country In ("England","Fra nce")
Country In ('England','Fra nce')
If numeric, like an ID number
CountryID In (1,2)

If a combo, there's only 1 selection. In listboxes, depending on the
setup, you can have 1 or many. I suppose you have it set up to Simple
or Extended for many options.

Use a ' or " to surround strings. Use # for dates. Nothing for numbers.

Here's some sample code to get you going.
Private Sub cmdPreview_Clic k()

Dim var As Variant
Dim strWhere As String
Dim strC As String 'Country
Dim strG As String 'Grooup
Dim strX As STring 'Combobox
Dim strQ As STring

strDoc = "rptContact "
strQ = """"

'string example. Gets second column.
With Me.CountrySelec t
For Each var In .ItemsSelected
strC = strC & strQ & .Column(1,var) & strQ & ","
Next
strC = "Country IN (" & Left(strC,len(s trC)-1) & ")"
End With

'numeric example. Gets first column.
With Me.GroupSelect
For Each var In .ItemsSelected
strG = strG & .Column(0,var) & ","
Next
strG = "GroupID IN (" & Left(strG,len(s trG)-1) & ")"
End With

'Combo box example as a string value (uses a ' to surround string)
If not IsNull(Me.COmbo boxName) then
strX = "SomeFldNam e = '" & Me.ComboBox & "'"
Endif

If strC "" Then strWhere = strC & " And "
If strG "" then strWhere = strWhere & strG & " And "
If strX "" then strWhere = strWhere & strX & " And "

'remove trailing " And " in strWhere
If strWhere "" then strWhere = Left(strWhere,l en(strWhere)-5)

'next 2 lines for testing purposes only
msgbox strWhere
Debug.print strWhere

DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere
End Sub

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

All the Listboxes are Simple Multi Listboxes.

Users will be adding fields that will appear in these listboxes, so
it's impossible to sepcify the Variables, ie Country In
("England","Fra nce") or Country In ("1","2"). I want users
will be able to select multiple Countries and Multiple Categories and
the report to only pull through Appropriate Contacts.

So i have this code, which i still can't get to work, though i'm sure
the solution is easy enough:-

Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelec t As String
Dim strCategorySele ct As String
Dim strWhere As String

strDoc = "rptContact "

With Me.CountrySelec t
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelec t = strCountrySelec t & strDelim
& .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountryS elect) - 1
If lngLen 0 Then
strCountrySelec t = "[CountryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If



With Me.CategorySele ct
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySele ct = strCategorySele ct &
strCategorySele ct & strDelim & .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategory Select) - 1
If lngLen 0 Then
strCategorySele ct = "[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If


If strCountrySelec t "" Then strWhere = strWhere &
strReligionSele ct & " And "
If strCategorySele ct "" Then strWhere = strWhere &
strCategorySele ct & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)



If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub

[Salad]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

micarl@gmail.co m wrote:

All the Listboxes are Simple Multi Listboxes.
>
Users will be adding fields that will appear in these listboxes, so
it's impossible to sepcify the Variables, ie Country In
("England","Fra nce") or Country In ("1","2"). I want users
will be able to select multiple Countries and Multiple Categories and
the report to only pull through Appropriate Contacts.
>
So i have this code, which i still can't get to work, though i'm sure
the solution is easy enough:-

First thing, do you even know what the value of strWhere is before you
open the report from your code below?

Are you getting errors? Then comment out your OnError line.

Do you even know if the data from strWhere is correct?

Are you collecting number or alphas when you create the criteria?

Do you know how to concatenate strings together?

What is strReligion? Pray to Jesus that it might work and hope for a
miracle?

Why do you mix/match ItemData and Column in your For/Nexts?

I have no idea what "i still can't get to work" means. This isn't a
mindreaders club. You really need to spend some time and THINK!

>
Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler
>
Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelec t As String
Dim strCategorySele ct As String
Dim strWhere As String
>
strDoc = "rptContact "
>
With Me.CountrySelec t
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelec t = strCountrySelec t & strDelim
& .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With
>
lngLen = Len(strCountryS elect) - 1
If lngLen 0 Then
strCountrySelec t = "[CountryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If
>
>
>
With Me.CategorySele ct
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySele ct = strCategorySele ct &
strCategorySele ct & strDelim & .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With
>
lngLen = Len(strCategory Select) - 1
If lngLen 0 Then
strCategorySele ct = "[CategoryID] IN (" & Left$(strWhere,
lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If
>
>
If strCountrySelec t "" Then strWhere = strWhere &
strReligionSele ct & " And "
If strCategorySele ct "" Then strWhere = strWhere &
strCategorySele ct & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)
>
>
>
If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
>
DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip
>
Exit_Handler:
Exit Sub
>
Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

Thanks again for your reply.

I am not reporting on just one Listbox that has several columns.

I want to print a report based on more than 1 multi select listbox.
One list box is called CategorySelect with CategoryID as the selection
field
The other is called CountrySelect with CountryID as the selection
field

I can base the report on CountrySelect using the code i posted first.

Can you base a report on more than one Multi Select Listbox?

Thanks

[Salad]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

micarl@gmail.co m wrote:

Thanks again for your reply.
>
I am not reporting on just one Listbox that has several columns.
>
I want to print a report based on more than 1 multi select listbox.
One list box is called CategorySelect with CategoryID as the selection
field
The other is called CountrySelect with CountryID as the selection
field
>
I can base the report on CountrySelect using the code i posted first.
>
Can you base a report on more than one Multi Select Listbox?
>
Thanks

Yes, you can.

I'd suggest studying the code I posted earlier this morning. Use that
as a "template" for you to experiment with. I'd suggest knowing what
the value is of strWhere as a filter prior to passing it to the report.
I'd suggest you compile the code. I'd suggest you debug.print the
strWhere and see if that filter works in the report by opening the
report and seeing if that filter works.

[Chuck]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxes to report

On Thu, 13 Nov 2008 04:49:11 -0800 (PST), micarl@gmail.co m wrote:

>Thanks again for your reply.
>
>I am not reporting on just one Listbox that has several columns.
>
>I want to print a report based on more than 1 multi select listbox.
>One list box is called CategorySelect with CategoryID as the selection
>field
>The other is called CountrySelect with CountryID as the selection
>field
>
>I can base the report on CountrySelect using the code i posted first.
>
>Can you base a report on more than one Multi Select Listbox?
>
>Thanks

Yes you can. I'll bet that some of the professional database programmers in
this news group will have examples in their web sites. However, there is a way
to accomplish this without writing a lot of code because I don't write code.

You can make several intermediate tables. Get your data for the first list box
from your main table. Append data to intermediate table 1 (with append query)
based on the selections of the first list box. Get your data for the second
list box from intermediate table 1. Append data to intermediate table 2 based
on the selections of the second list box. Get your data for the third list box
from intermediate table 2. etc. This is not pretty, but it works. Before
starting a new set of selections, delete all data from all intermediate tables
but leave the empty tables in the database so they don't have to be created
every time you use them. I have a small (228KB) sample database (A97) that
uses 3 cascading listboxes. It can be zipped a little smaller. I stopped
sending zipped files to folks who don't know me because they don't know if a
virus is getting into my zipped files. I can send it as an attachment in E-mail
if you want, either straight or zipped.

Chuck

[micarl@gmail.com]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

Excellent. Thanks again for your help. I see where i hadn't changed
the strWhere to strCountry.

Now working with code:-
Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strCountrySelec t As String
Dim strCategorySele ct As String
Dim strWhere As String
strDoc = "rptContactRepo rt"

With Me.CountrySelec t
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCountrySelec t = strCountrySelec t & strDelim
& .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCountryS elect) - 1
If lngLen 0 Then
strCountrySelec t = "[CountryID] IN (" & Left$
(strCountrySele ct, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If



With Me.CategorySele ct
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strCategorySele ct = strCategorySele ct &
strCategorySele ct & strDelim & .ItemData(varIt em) & strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
End If
Next
End With

lngLen = Len(strCategory Select) - 1
If lngLen 0 Then
strCategorySele ct = "[CategoryID] IN (" & Left$
(strCategorySel ect, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories : " & Left$(strDescri p, lngLen)
End If
End If

If strCountrySelec t "" Then strWhere = strWhere &
strCountrySelec t & " And "
If strCategorySele ct "" Then strWhere = strWhere &
strCategorySele ct & " And "
If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)



If CurrentProject. AllReports(strD oc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
End If
Resume Exit_Handler
Debug.Print strWhere
End Sub

[bobh]

Re: Multiple Combo Boxes as well as multiple Multi select List Boxesto report

Hi,
1. What is the value of strDelim??? since you are looking up text(I
assume) you need at a minimum a single quot(tick) mark around each
text value and I don't see that in your code.

2. Have you stepped through this code in 'debug' mode and looked at(in
immediate window) the string it builds? if so post it.

bobh.


On Nov 13, 9:42 am, mic...@gmail.co m wrote:

Excellent. Thanks again for your help. I see where i hadn't changed
the strWhere to strCountry.
>
Now working with code:-
Private Sub cmdPreview_Clic k()
On Error GoTo Err_Handler
>
    Dim varItem As Variant
    Dim strDescrip As String
    Dim lngLen As Long
    Dim strDelim As String
    Dim strDoc As String
    Dim strCountrySelec t As String
    Dim strCategorySele ct As String
    Dim strWhere As String
    strDoc = "rptContactRepo rt"
>
    With Me.CountrySelec t
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strCountrySelec t = strCountrySelec t & strDelim
& .ItemData(varIt em) & strDelim & ","
                 strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
            End If
        Next
    End With
>
    lngLen = Len(strCountryS elect) - 1
    If lngLen 0 Then
        strCountrySelec t = "[CountryID] IN (" & Left$
(strCountrySele ct, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen 0 Then
            strDescrip = "Categories : " & Left$(strDescri p,lngLen)
        End If
    End If
>
    With Me.CategorySele ct
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strCategorySele ct = strCategorySele ct &
strCategorySele ct & strDelim & .ItemData(varIt em) & strDelim & ","
                 strDescrip = strDescrip & """" & .Column(1, varItem)
& """, "
            End If
        Next
    End With
>
    lngLen = Len(strCategory Select) - 1
    If lngLen 0 Then
        strCategorySele ct = "[CategoryID] IN (" & Left$
(strCategorySel ect, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen 0 Then
            strDescrip = "Categories : " & Left$(strDescri p,lngLen)
        End If
    End If
>
        If strCountrySelec t "" Then strWhere = strWhere &
strCountrySelec t & " And "
        If strCategorySele ct "" Then strWhere = strWhere &
strCategorySele ct & " And "
        If strWhere "" Then strWhere = Left(strWhere, Len(strWhere)
- 5)
>
    If CurrentProject. AllReports(strD oc).IsLoaded Then
        DoCmd.Close acReport, strDoc
    End If
>
   DoCmd.OpenRepor t strDoc, acViewPreview, WhereCondition: =strWhere,
OpenArgs:=strDe scrip
>
Exit_Handler:
    Exit Sub
>
Err_Handler:
    If Err.Number <2501 Then
        MsgBox "Error " & Err.Number & " - " & Err.Description , ,
"cmdPreview_Cli ck"
    End If
    Resume Exit_Handler
Debug.Print strWhere
End Sub