manipulate query result and display on report with VBA

Question

I want to manipulate data that I get from an sql query then write it to a report all this done with VBA in MS Access.

So first i need to get the data I need with this sql query

SELECT test.number_id FROM test WHERE ((test.number_id)>30));

need to save the output in a variable let say

Dim testVar As Int

and make my calculations

then i need to display the result in the report.

Anyone know if thats possible and how to do this???

Grant Grant · Accepted Answer · 2013-07-24T17:37:29

You can set your SQL statement to a recordset and then manipulate the results in there.

Dim myR2 As Recordset
Dim strSQL as String

strSQL = "SELECT test.number_id FROM test WHERE test.number_id>30"

Set myR = CurrentDb.OpenRecordset("strSQL", dbOpenDynaset)

'Manipulate myR info here'
myR.MoveFirst 'so you start from the first record'
myR.MoveNext 'to move to the next record; handy in a loop'
myR.FindFirst 'find a record in that recordset'
myR![FieldName] 'to call upon that record's field'
'Or use CREATE statement to create a new table and generate a report from it'

Set myR = Nothing

manipulate query result and display on report with VBA

1 Answers