passing a variable to OpenReport WhereClause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2000 (an adp file) with a MS SQL Server back end

I would like to open a report dynamically for specified records and I am using the following line of code

DoCmd.OpenReport "my_report", acViewPreview, , "[my_field] = ' " & @my_variable & " '

From the immediate window, I can tell that the where clause (above) is displaying correctly. eg. [my_field] = '1234
However, the report takes forever to run and the PC runs out of memory before it can display the report. The table the report is based on has over a million records in it. So, I suspect that the report is trying to retrieve all the records in the table and then apply the where clause to filter out what I don't want.... hence the poor performance / no response. If I put the where clause in the stored procedure that the report is based on, the report runs very fast - as expected. However, I don't want to have to type the where clause criteria in every time this loop of code runs. (not practicle)

Does anyone have a suggestion on how I can make this work? Any help would be greatly appreciated

Thank you
JM
 
correction.... my line of code is as follows

DoCmd.OpenReport "my_report", acViewPreview, , "[my_field] = ' " & my_variable & " '

(no @ sign
Sorry about that
JMC
 
Back
Top