Push FilterbyForm results to Report

[Guest]

Hi,
I've found some code to run an exisiting report based on FilterbyForm
results. My criteria is text, but I think the code was written for a number.
Can anyone tell me how to fix it so it works? I think the quotes are my
problem. here's the code:

Function TBEmployeeRptFacFilter()
'run report based on filter
Dim strIDs As String
Dim rs As DAO.Recordset
Set rs = Forms!frmFacultyIncrease.RecordsetClone
Do While Not rs.EOF
strIDs = strIDs & rs!EID & ","
rs.MoveNext
Loop
'get rid of trailing comma
If Len(strIDs) > 0 Then strIDs = Left$(strIDs, Len(strIDs) - 1)
'open report w/IDs
strCriteria = "EID in ('" & strIDs & "')"

DoCmd.OpenReport "rptFacultyEmployee", acViewPreview, , strCriteria

End Function

-----After I run it and the report has no record, this is what is in the
Filter property:
(EID in ('859007645,859007646'))

 

[Guest]

Is the EID field in your table Text or Numeric?

Barry

 

[Guest]

TEXT - I can't figure out how to format it properly

 

[Guest]

Your string needs to have quotes around each value. Try this:

Function TBEmployeeRptFacFilter()
'run report based on filter
Dim strIDs As String
Dim rs As DAO.Recordset
Set rs = Forms!frmFacultyIncrease.RecordsetClone
Do While Not rs.EOF
strIDs = strIDs & "'" & rs!EID & "',"
rs.MoveNext
Loop
'get rid of trailing comma
If Len(strIDs) > 0 Then strIDs = Left$(strIDs, Len(strIDs) - 1)
'open report w/IDs
strCriteria = "EID in (" & strIDs & ")"

DoCmd.OpenReport "rptFacultyEmployee", acViewPreview, , strCriteria

End Function

I added a single quote around each iteration of the loop and removed it from
the "strCritera=" statement.

HTH,
Barry

 

[Guest]

works like a charm! thanks

Your string needs to have quotes around each value. Try this:

Function TBEmployeeRptFacFilter()
'run report based on filter
Dim strIDs As String
Dim rs As DAO.Recordset
Set rs = Forms!frmFacultyIncrease.RecordsetClone
Do While Not rs.EOF
strIDs = strIDs & "'" & rs!EID & "',"
rs.MoveNext
Loop
'get rid of trailing comma
If Len(strIDs) > 0 Then strIDs = Left$(strIDs, Len(strIDs) - 1)
'open report w/IDs
strCriteria = "EID in (" & strIDs & ")"

DoCmd.OpenReport "rptFacultyEmployee", acViewPreview, , strCriteria

End Function

I added a single quote around each iteration of the loop and removed it from
the "strCritera=" statement.

HTH,
Barry