Two multi-select list boxes

[Guest]

Could I use two multi-select list boxes for a report prepared by the
conditions of two fields of selected items ?

Best Regards

 

[Allen Browne]

Yes, you can read data from 2 unbound multi-select list boxes, to make up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

 

[Guest]

Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page, it
states that this typically indicates that variant used to print arrays. It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

Yes, you can read data from 2 unbound multi-select list boxes, to make up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Could I use two multi-select list boxes for a report prepared by the
conditions of two fields of selected items ?

Best Regards

 

[Allen Browne]

Comment out the error handler, i.e. add a single quote mark in front of the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you can see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page, it
states that this typically indicates that variant used to print arrays. It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

Yes, you can read data from 2 unbound multi-select list boxes, to make up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by the
conditions of two fields of selected items ?

Best Regards

 

[Guest]

Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2" were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for the first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric

Comment out the error handler, i.e. add a single quote mark in front of the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you can see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page, it
states that this typically indicates that variant used to print arrays. It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

Yes, you can read data from 2 unbound multi-select list boxes, to make up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by the
conditions of two fields of selected items ?

Best Regards

 

[Allen Browne]

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2" were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric

Comment out the error handler, i.e. add a single quote mark in front of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?

Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page, it
states that this typically indicates that variant used to print arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes, to make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by
the
conditions of two fields of selected items ?

 

[Guest]

Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the problem.
Thanks again.

Siric

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2" were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric

Comment out the error handler, i.e. add a single quote mark in front of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page, it
states that this typically indicates that variant used to print arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes, to make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by
the
conditions of two fields of selected items ?

 

[Allen Browne]

Did you try it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the problem.
Thanks again.

Siric

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2

Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2"
were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric



:

Comment out the error handler, i.e. add a single quote mark in front
of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you
can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page,
it
states that this typically indicates that variant used to print
arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes, to
make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like
this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by
the
conditions of two fields of selected items ?

 

[Guest]

Yes, I had done it successfully.

Siric

Did you try it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the problem.
Thanks again.

Siric

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2


Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2"
were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric



:

Comment out the error handler, i.e. add a single quote mark in front
of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so you
can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when the
cmdPreview_click executed. By looking at the notes of the help page,
it
states that this typically indicates that variant used to print
arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes, to
make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like
this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN (1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared by
the
conditions of two fields of selected items ?

 

[Allen Browne]

Ah: it worked, but you needed an explanation?

The end result you are after looks like the WHERE clause of a query. If you
create a query that has Criteria under 2 fields, and then switch it to SQL
View (View menu), you will see this kind of thing:
(TypeID = 9) AND (Surname = "Jones")
The brackets are not crucial, and Access often inserts others.

In your example, you created 2 strings and needed to join them together. We
have to insert the AND between them, and so:
strWhere & " AND " & strWhere2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I had done it successfully.

Siric

Did you try it?

Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the
problem.
Thanks again.

Siric


:

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2


Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2"
were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for
the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric



:

Comment out the error handler, i.e. add a single quote mark in
front
of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so
you
can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when
the
cmdPreview_click executed. By looking at the notes of the help
page,
it
states that this typically indicates that variant used to print
arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for
the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes,
to
make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like
this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN
(1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared
by
the
conditions of two fields of selected items ?

 

[Guest]

Thank you very much for the explanation of the magic code.

I just could not understand why the code (WhereCondition:=strWhere AND
strWhere2 )used was nearly the same as the SQL code (Where strWhere And
strWhere2), but it had never worked in VBA. As explained by you, a text
insertion for the conjunction operation must be required.

Siric

Ah: it worked, but you needed an explanation?

The end result you are after looks like the WHERE clause of a query. If you
create a query that has Criteria under 2 fields, and then switch it to SQL
View (View menu), you will see this kind of thing:
(TypeID = 9) AND (Surname = "Jones")
The brackets are not crucial, and Access often inserts others.

In your example, you created 2 strings and needed to join them together. We
have to insert the AND between them, and so:
strWhere & " AND " & strWhere2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I had done it successfully.

Siric

Did you try it?


Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the
problem.
Thanks again.

Siric


:

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2


Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2"
were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for
the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric



:

Comment out the error handler, i.e. add a single quote mark in
front
of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so
you
can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when
the
cmdPreview_click executed. By looking at the notes of the help
page,
it
states that this typically indicates that variant used to print
arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for
the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes,
to
make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like
this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN
(1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared
by
the
conditions of two fields of selected items ?

 

[Guest]

Hi Allen,

Your posts with Siric have been very helpful to me. I have followed your
instructions but i can not get the the 2 listboxes to pull a report using
strWhere and strWhere2. It works great with one listbox but can't get two to
work. Any advice would be greatly appreciated, if possible.

Thanks
Tim

Ah: it worked, but you needed an explanation?

The end result you are after looks like the WHERE clause of a query. If you
create a query that has Criteria under 2 fields, and then switch it to SQL
View (View menu), you will see this kind of thing:
(TypeID = 9) AND (Surname = "Jones")
The brackets are not crucial, and Access often inserts others.

In your example, you created 2 strings and needed to join them together. We
have to insert the AND between them, and so:
strWhere & " AND " & strWhere2

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, I had done it successfully.

Siric

Did you try it?


Dear Allen,

Yes, it is the right spot.
Grateful if you could explain further how this code can solve the
problem.
Thanks again.

Siric


:

Did you mean to concatenate the 2 where strings together like this:
DoCmd.OpenReport strDoc, acViewPreview, _
WhereCondition:=strWhere & " AND " & strWhere2


Dear Alen,

The error no 13 of "Type mismatch" occured in the following line:

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
strWhere2

Where "strWhere2", "varitem2", strDescrip2", "lngLen2", strDelim2"
were
defined as the parameters for the selected items of the second list.

The report to be opened was grouped by two fields which stand for
the
first
and second lists.

The compilation was completed without problem.

The database runs on Access 97.

Thanks

Siric



:

Comment out the error handler, i.e. add a single quote mark in
front
of
the
first line like this:
'On Error Goto ...
You generally want to do this until you get the code working, so
you
can
see
which line is causing the problem.

Can you see which line is causing the error, and what is wrong?

Choose Compile from the Debug menu.
Does it compile okay?


Dear Allen,

I was prompted by the error message no 13 "Type Mismatch" when
the
cmdPreview_click executed. By looking at the notes of the help
page,
it
states that this typically indicates that variant used to print
arrays.
It
jumped into the MsgBox "..." of the Err_Handler subroutine for
the
faultfinding. I got stuck to find the way to solve it.

Thanks.

:

Yes, you can read data from 2 unbound multi-select list boxes,
to
make
up
the WhereCondition for the OpenReport action.

In the end, the WhereCondition string will look something like
this:
"([Category] IN (""dog"", ""cat"")) AND ([SomeID] IN
(1,4,7))"

For the steps to get there, see:
Use a multi-select list box to filter a report
at:
http://members.iinet.net.au/~allenbrowne/ser-50.html

Could I use two multi-select list boxes for a report prepared
by
the
conditions of two fields of selected items ?