Adding selected records from a populated recordset to an empty recordset

[Flo100]

Hi,

I have a recordset which I have populated with a query.
I have an empty recordset.

I would like to take value of a field in every single record, which is a string.
Search if the 4rth letter of the string is an 'a', and if yes add that record to the new recordset. Finally i want to output the resultset onto a report.

I need some help please.

[ADezii]

Hi,

I have a recordset which I have populated with a query.
I have an empty recordset.

I would like to take value of a field in every single record, which is a string.
Search if the 4rth letter of the string is an 'a', and if yes add that record to the new recordset. Finally i want to output the resultset onto a report.

I need some help please.

The Logic is listed below, I based it on a [LastName] Field in a Table named tblEmployees:
[CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset, rstNew As DAO.Recordset
Dim strSQL As String

strSQL = "Select [LastName] From tblEmployees Where Mid$([LastName], 4, 1) = 'a' Order By [LastName]"

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)

'For testing purposes - works fine!
Do While Not MyRS.EOF
Debug.Print MyRS![LastName]
MyRS.MoveNext
Loop

Set Reports("Your Report Name Here").Recordse t = MyRS

'In reality, you would probably not want to Close the Recordset used for the Report
'prior to Opening it and you would also want to Declare MyRS at a Modular level
'MyRS.Close
'Set MyRS = Nothing[/CODE]

[Flo100]

Thank you very much. It worked!

[ADezii]

Thank you very much. It worked!

Glad it all worked out for you.