I responded to a post of a similar type back a while ago, and I did mean to write an FAQ for it then - guess I'll do it now though - better late than ever.
The following is my preferred method of handling the variable parameter - as the French say, "Chacun son gout!"
We will need to create the following variables:
Dim dbs As Database Dim strSQL As String Dim strQueryName As String Dim qryDef As QueryDef
'(** of course, you can name the variables whatever you want, as long as you remain consistent. :) ) On Error Resume Next 'set variable values Set dbs = <database path or CurrentDb> strQueryName = "<Name of Query Here>" 'delete old query, if it exists dbs.QueryDefs.Delete strQueryName strSQL = "SELECT <table name>.<field1>, <tablename>.<field2> ... <tablename>.<fieldX> FROM <tablename> WHERE <tablename>.<fieldname> LIKE '" & <variablename> "';"
Set qrydef = dbs.CreateQueryDef(strQueryName, strSQL)
And you're finished ... but I'm guessing that all those < and > and <field ... > statements may be pretty confusing ... so, let's use a real-time example and show you how the process really works.
Example: Bob needs to dynamically create a report for a month of the year that his users enters on a form. His table (called BobTable) contains a month field (called BobMonth) and a currency field (BobBucks). Once he extracts the data into a query (called BobQuery), he will need to open his report in preview mode (report is called - oh, you guessed it, BobReport - man, I use really stupid names! ) ... Of course, we're assuming that Bob has done all his error checking, and the month that the user selected has been assigned to a string variable called strMonth (see? A normal name!)
On Error Resume Next
Dim dbs As Database Dim strSQL As String Dim strQueryName As String Dim qryDef As QueryDef 'set variable values Set dbs = CurrentDb strQueryName = "BobQuery" 'Delete old query first - we want fresh data! dbs.QueryDefs.Delete strQueryName 'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us. strSQL = "SELECT BobTable.BobMonth, BobTable.BobBucks FROM BobTable " _ & "WHERE BobTable.BobMonth LIKE '" & strMonth & "';" 'Create query definition Set qrydef = dbs.CreateQueryDef(strQueryName, strSQL) 'Open report for viewing Docmd.OpenReport "BobReport", acViewPreview ' *** End of Code ***
Hope this wasn't TOO confusing for you - I tried to be as detailed as possible - if you need assistance with this code, please feel free to leave me a post.
PS: Oh, one last thing! You will need to set your report recordsource to the name of the query you created. This way, your report will always contain the data required.