Using Variables In SQL Select Statement

[maxflitom]

Hello Tek-Tips and thank you again for taking the time to read my question. Is there a way to declare a variable in a SQL Select statement which will allow me to assign through VB6. I am generating a SQL statement which uses 4 dates as parameters. However these 4 dates are needed several times in the SQL statement. Instead of using a question mark for each one of them, can I declare a variable in the Select statement, assign this variable, and use it in the SQL code?

For example:
Declare @Date1 DATE
Declare @Date2 Date

SELECT Revenue FROM MyTable WHERE Date BETWEEN @Date1 AND @Date2

The SQL statement is simplified for this question. I want to be able to assign @Date1 and @Date2 through my code. Does Access 2002 allow for this?

Any suggestions are greatly appreciated.

Tom (maxflitom)

 

[SkipVought]

Hi

s = "SELECT Revenue FROM  MyTable WHERE Date BETWEEN '" & @Date1 & "' AND '" & @Date2 & "'"

then assing s to the SQL property.

You may need to use the Format function to be certain that the compare works properly...

s = "SELECT Revenue FROM  MyTable WHERE Format(Date, "mm/dd/yyyy") BETWEEN '" & Format(@Date1, "mm/dd/yyyy") & "' AND '" & Format(@Date2, "mm/dd/yyyy") & "'"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[maxflitom]

SkipVought,

Thank you for responding. I dug a little further and found what I am looking for. Access has a Parameters keyword used to define input parameters. The syntax for this question would be:

PARAMETERS @Date1 DATE, @Date2 DATE;

SELECT Revenue FROM MyTable WHERE Date BETWEEN @Date1 AND @Date2

Tom (maxflitom)

 

[scriverb]

maxflitom: Although the Parameters clause allows you to delcare a prameter name for in SQL it does not allow for the passing of data from your code to the SQL. If I am reading your question correctly you want to be able to assign values to variables and have them used within your SQL. The Parameters statement is only going to declare a variable for that SQL statement but the query will still prompt you for the values.

If you want to assign values to variables and then use them in SQL you must use a global variable and then call up the gloval variable value with the use of a function. See code below:

Copy and paste the following in a database Module:

Global  @Date1 as Date
Global  @Date2 as Date

Public Function DT_@Date1 as Date
DT_@Date1 = @Date1
End Function 

Public Function DT_@Date2 as Date
DT_@Date2 = @Date2
End Function

Here is your SQL using your example:

SELECT Revenue FROM  MyTable WHERE Date BETWEEN [HIGHLIGHT]DT_@Date1()[/HIGHLIGHT] AND [HIGHLIGHT]DT_@Date2()[/HIGHLIGHT]

End Function [/CODE]

Here is your code making the variable assignments and calling the query:

@Date1 = #05/28/2004#
@Date2 = #05/29/2004#
DoCmd.OpenQuery "[i]yourqueryname[/i]"

With this setup you can make any assignment of date values within your code and then call the query. The function calls in the query then execute and pull in the datevalues. With your previous example with the Parameters clause each time you run the query the user would be prompted for the date values. The parameters statement is doing nothing more then assigning a variable type to the variable name being declared. There still is no value assigned, thus the prompting.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[maxflitom]

shirverb,

Thank you for responding. Maybe I did not word my question properly. I wanted to create input variables within my SQL statement to be used withing the SELECT Statement. I am writing a SQL statement that will require several input variables in order to retrieve the requested results. Many of these input variables will be used multiple times and I did not want the use the ?. I wanted to declare 5 variables, four of these will be dates and will be used throughout the statement. If I can declare Input variables, I can use these instead of assigning the ? in the SQL statement. When I get my SQL statement completed, after the holiday, I will post it for you to see.

Thanks again!

Tom (maxflitom)

 

[dandot]

Spartan.... is there a way to do this local variables. What if a person is using a network Back end database being accessed by as many as 30 users simulataneously.

 

[scriverb]

dandot: I am no longer on a network to check this out but I believe this to be true. Normally, there is a back-end database that holds your changing data tables. In addition each of your users has a front-end database that has your static tables, forms, reports, queries, and modules. The FE database accesses the BE database through a LINKED table connection.

Now with this configuration, the global variables that I demonstrated above will be independent to each user. They are considered global within each users environment so that when you store a value in them on a form it stays there and can be accessed by other forms, reports, and module code.

Is this the way you have your database setup? Or, by chance do you have a single database on the server with 30+ users all accessing it?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]