List Box to Filter Report

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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
 
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...
 
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...
 
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...
 
Back
Top