Using a variable in Where Clause in Query

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I have a query to return the weeks in the year. I'm trying to use the public
variable iDefaultReportYear in the where part to limit the returned records
to the year a user sets the iDefaultReportYear variable to.

What I can't understand is what syntax to enclose the variable with. I've
tried () and [] to no avail.

Can anyone tell me how to change the below sql statement to treat
iDefaultReportYear as a variable and not a table, etc.?

SELECT DISTINCT WeekNumber([prod_date]) AS [Week #],
Format([prod_Date],"yyyy") AS [Year]
FROM tblDate
WHERE (Format([prod_Date],"yyyy"))=("iDefaultReportYear");
 
I have a query to return the weeks in the year. I'm trying to use the public
variable iDefaultReportYear in the where part to limit the returned records
to the year a user sets the iDefaultReportYear variable to.

What I can't understand is what syntax to enclose the variable with. I've
tried () and [] to no avail.

Can anyone tell me how to change the below sql statement to treat
iDefaultReportYear as a variable and not a table, etc.?

SELECT DISTINCT WeekNumber([prod_date]) AS [Week #],
Format([prod_Date],"yyyy") AS [Year]
FROM tblDate
WHERE (Format([prod_Date],"yyyy"))=("iDefaultReportYear");
Click to expand...

VBA variables are in one domain; SQL queries are in a different one.
SQL queries have no way to know what variables might be defined in
some module - the information simply isn't available.

The solution is to write a little wrapper Function to return the year,
and call that function from the query:

Public Function GetYear() As Integer
GetYear = iDefaultReportYear
End Function

SELECT DISTINCT WeekNumber([prod_date]) AS [Week #],
Year([prod_Date]) AS [Year]
FROM tblDate
WHERE (Year([prod_Date])=GetYear();


Note that I'm using the more efficient built-in Year function rather
than casting the year as a string and then converting it.
 
Thanks for clarifying. I already had a function done, I just wanted to run
it once on startup to avoid trips to the db.


John Vinson said:
I have a query to return the weeks in the year. I'm trying to use the public
variable iDefaultReportYear in the where part to limit the returned records
to the year a user sets the iDefaultReportYear variable to.

What I can't understand is what syntax to enclose the variable with. I've
tried () and [] to no avail.

Can anyone tell me how to change the below sql statement to treat
iDefaultReportYear as a variable and not a table, etc.?

SELECT DISTINCT WeekNumber([prod_date]) AS [Week #],
Format([prod_Date],"yyyy") AS [Year]
FROM tblDate
WHERE (Format([prod_Date],"yyyy"))=("iDefaultReportYear");
Click to expand...

VBA variables are in one domain; SQL queries are in a different one.
SQL queries have no way to know what variables might be defined in
some module - the information simply isn't available.

The solution is to write a little wrapper Function to return the year,
and call that function from the query:

Public Function GetYear() As Integer
GetYear = iDefaultReportYear
End Function

SELECT DISTINCT WeekNumber([prod_date]) AS [Week #],
Year([prod_Date]) AS [Year]
FROM tblDate
WHERE (Year([prod_Date])=GetYear();


Note that I'm using the more efficient built-in Year function rather
than casting the year as a string and then converting it.
Click to expand...
 
Hello Scott,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

In T-SQL, you can use the variable directly in the SQL statement and apply the variable in the
following way:

----------------------------------------------------------
SELECT colName FROM tblName WHERE colName=@variable
-----------------------------------------------------------

In Jet SQL, you can kludge the variable in one statement and use DoCmd.RunSQL to execute
it, or just call the wrapper Function in your statement as John pointed out.

If you'd like to like it run it when opening the database, you'd put the statement in the startup
module or configure the startup form in the Startup dialog.

Scott, does this answer your question? If there is anything more I can do to assist you, please
feel free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top