List Box to Filter Report

G

Guest

I'm trying to create a string of values from a list box and pass this to the
WHERE condition to open a report.

I've tried code given in previous posted answers, but when adapting it to my
own DB, I'm going wrong somewhere.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strSQL = "[FunderProgramme]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [FunderProgramme]="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 22)

DoCmd.OpenReport "rptFunder1", acViewPreview, , "([strSQL])"


The problems:
1) The example I took this from had to truncate 12 characters off the string
as it was " OR [EmpID]=". If I change this to 22 because my code adds " OR
[FunderProgramme]=" I get an error message "invalid call or procedure".
2) If I leave the truncate as "strSQL = Left$(strSQL, Len(strSQL) - 12), I
get a message box asking me for the value of "strSQL".

Any pointers gratefully received.

thanks

Winger
 
G

Guest

Hi,

Try this

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strWhere As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strWhere = ""

For Each varItem In ctl.ItemsSelected
strWhere = iif(strSQL="", "[FunderProgramme]=" & ctl.ItemData(varItem),
" OR [FunderProgramme]=" & ctl.ItemData(varItem))
Next varItem


DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere
 
G

Guest

Sorry small correction


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strWhere As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strWhere = ""

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & iif(strSQL="", "[FunderProgramme]=" &
ctl.ItemData(varItem),
" OR [FunderProgramme]=" & ctl.ItemData(varItem))
Next varItem


DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere
 
G

Guest

JaRa,

I'm also assuming that in your code "iif(strSQL" should read "iif(strWhere"

Winger

JaRa said:
Sorry small correction


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strWhere As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strWhere = ""

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & iif(strSQL="", "[FunderProgramme]=" &
ctl.ItemData(varItem),
" OR [FunderProgramme]=" & ctl.ItemData(varItem))
Next varItem


DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Winger said:
I'm trying to create a string of values from a list box and pass this to the
WHERE condition to open a report.

I've tried code given in previous posted answers, but when adapting it to my
own DB, I'm going wrong somewhere.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strSQL = "[FunderProgramme]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [FunderProgramme]="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 22)

DoCmd.OpenReport "rptFunder1", acViewPreview, , "([strSQL])"


The problems:
1) The example I took this from had to truncate 12 characters off the string
as it was " OR [EmpID]=". If I change this to 22 because my code adds " OR
[FunderProgramme]=" I get an error message "invalid call or procedure".
2) If I leave the truncate as "strSQL = Left$(strSQL, Len(strSQL) - 12), I
get a message box asking me for the value of "strSQL".

Any pointers gratefully received.

thanks

Winger
Click to expand...
Click to expand...
 
G

Guest

Yes Indeed sorry
--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Winger said:
JaRa,

I'm also assuming that in your code "iif(strSQL" should read "iif(strWhere"

Winger

JaRa said:
Sorry small correction


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strWhere As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strWhere = ""

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & iif(strSQL="", "[FunderProgramme]=" &
ctl.ItemData(varItem),
" OR [FunderProgramme]=" & ctl.ItemData(varItem))
Next varItem


DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Winger said:
I'm trying to create a string of values from a list box and pass this to the
WHERE condition to open a report.

I've tried code given in previous posted answers, but when adapting it to my
own DB, I'm going wrong somewhere.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strSQL = "[FunderProgramme]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [FunderProgramme]="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 22)

DoCmd.OpenReport "rptFunder1", acViewPreview, , "([strSQL])"


The problems:
1) The example I took this from had to truncate 12 characters off the string
as it was " OR [EmpID]=". If I change this to 22 because my code adds " OR
[FunderProgramme]=" I get an error message "invalid call or procedure".
2) If I leave the truncate as "strSQL = Left$(strSQL, Len(strSQL) - 12), I
get a message box asking me for the value of "strSQL".

Any pointers gratefully received.

thanks

Winger
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

JaRa,

The code is now multiselecting, but I still get an error of "missing
operator" for each item selected.
I think some commas need to be inserted into the strWhere as they are text
values, but I still haven't got to grips with this yet.

Again, thanks for your time on this.

Winger



JaRa said:
Yes Indeed sorry
--
- Raoul Jacobs

The nature of developping is sharing knowledge.


Winger said:
JaRa,

I'm also assuming that in your code "iif(strSQL" should read "iif(strWhere"

Winger

JaRa said:
Sorry small correction


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strWhere As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strWhere = ""

For Each varItem In ctl.ItemsSelected
strWhere = strWhere & iif(strSQL="", "[FunderProgramme]=" &
ctl.ItemData(varItem),
" OR [FunderProgramme]=" & ctl.ItemData(varItem))
Next varItem


DoCmd.OpenReport "rptFunder1", acViewPreview, , strWhere

--
- Raoul Jacobs

The nature of developping is sharing knowledge.


:

I'm trying to create a string of values from a list box and pass this to the
WHERE condition to open a report.

I've tried code given in previous posted answers, but when adapting it to my
own DB, I'm going wrong somewhere.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmMatchRestoFund
Set ctl = frm!PrintList

strSQL = "[FunderProgramme]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [FunderProgramme]="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 22)

DoCmd.OpenReport "rptFunder1", acViewPreview, , "([strSQL])"


The problems:
1) The example I took this from had to truncate 12 characters off the string
as it was " OR [EmpID]=". If I change this to 22 because my code adds " OR
[FunderProgramme]=" I get an error message "invalid call or procedure".
2) If I leave the truncate as "strSQL = Left$(strSQL, Len(strSQL) - 12), I
get a message box asking me for the value of "strSQL".

Any pointers gratefully received.

thanks

Winger
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-Select list box as query criteria 6
Problem with multiple selection list box 4
Error in multiple select in a list box code 4
Multi-Select List Box 2
multi-select list box in sql statement issue 3
ListBox as criteria for query 6
Update the subform or refresh based on multiple selection in a lis 1
Regarding Subform update 1

Top