Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I use VB or VBA variables as query parameters?
Query Questions
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
[color green] '(** of course, you can name the variables whatever you want, as long as you remain consistent.
) [/color]
On Error Resume Next
[color green] 'set variable values [/color]
Set dbs = <database path or CurrentDb>
strQueryName = "<Name of Query Here>"
[color green]'delete old query, if it exists [/color]
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! LOL) ... 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!)
Anyways ...
[color green]'set variable values [/color]
[color green]'Delete old query first - we want fresh data![/color]
[color green]'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us. [/color]
[color green]'Create query definition [/color]
[color green]'Open report for viewing [/color]
[color green]' *** End of Code *** [/color]
...
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.
Greg
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.
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
[color green] '(** of course, you can name the variables whatever you want, as long as you remain consistent.
) [/color]On Error Resume Next
[color green] 'set variable values [/color]
Set dbs = <database path or CurrentDb>
strQueryName = "<Name of Query Here>"
[color green]'delete old query, if it exists [/color]
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! LOL) ... 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!)
Anyways ...
Code:
On Error Resume Next
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Code:
Set dbs = CurrentDb
strQueryName = "BobQuery"
Code:
dbs.QueryDefs.Delete strQueryName
Code:
strSQL = "SELECT BobTable.BobMonth, BobTable.BobBucks FROM BobTable " _
& "WHERE BobTable.BobMonth LIKE '" & strMonth & "';"
Code:
Set qrydef = dbs.CreateQueryDef(strQueryName, strSQL)
Code:
Docmd.OpenReport "BobReport", acViewPreview...
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.
Greg
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.