We help IT Professionals succeed at work.

Access 2007 Query Issues

juricta used Ask the Experts™
I need to set a variable that anyone in the program can use ANYTIME(Global CurrentYear??).  I DIM'd this variable in a MODULE called GlobalCode. I need this variable to be set to YEAR(DATE) BEFORE the first form is opened/loaded/??.  When the first form opens there is a sub-form of Projects.  I want the recordset of this SUBFORM  to filter on [Year(Date)] so, if I open the form today, the only records that show are records from 2012.  I have a query that will do this and it works IF I put 2012 in the CRITERIA of the query but when I put the code in the CRITERIA it gives me ZERO record.  Any idea what I am doing wrong?  
The field I want to filteron is ProjectEndDate (31-Dec-2012) which is in the PROJECTS table.  This field is on a SUB_FORM (Client Subform) which is subordinate to the main form (Client).   Here iis the entire SQL of the query:

SELECT DISTINCTROW [Sum of Payments Query].[Total Payments], Projects.ProjectBeginDate, Projects.ClientID, Projects.ProjectID, Projects.ProjectName, [Hour by Project].[Total Hours], [Hour by Project].[Billing Amount], [Expenses by Project].[Total Expenses], Projects.ProjectName, (Year([Projects]![ProjectEndDate])) AS Expr1
FROM (([Sum of Payments Query] RIGHT JOIN Projects ON [Sum of Payments Query].ProjectID = Projects.ProjectID) LEFT JOIN [Hour by Project] ON Projects.ProjectID = [Hour by Project].ProjectID) LEFT JOIN [Expenses by Project] ON Projects.ProjectID = [Expenses by Project].ProjectID
WHERE ((((Year([Projects]![ProjectEndDate])))="CurrentYear"))WHERE ((((Year([Projects]![ProjectEndDate])))=" & CurrentYear & "))
ORDER BY [Sum of Payments Query].[Total Payments], Projects.ProjectBeginDate DESC , Projects.ProjectName;
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can't directly use variables in queries, but you can create function and call it.
In Module:
Public Function CurrentYear() as integer
CurrentYear = Year(Date)
end Function
WHERE ((((Year([Projects]![ProjectEndDate])))="CurrentYear"))WHERE ((((Year([Projects]![ProjectEndDate])))=" & CurrentYear & "))

This is invalid sql- I don't know how you get anything to work!

You cannot reference a variable in a saved query.  You need to build a function that returns the value..

Public Function GetCurrYr()
getCurryr= Currentyear
End function

Then you can set the criteria for the project year to GetCurrYr()


Understand the function.  How do I call it and from what event? SUBFORM OnOpen? This SUBFORM data changes as I move form record to record on the MAINFORM
You can call it anywhere (from query, sub, other function of assign it to field on a form or report)


Last bit of help.  If I want to use this in a query in the WHERE clause, what is the code?  I would need to set

Year([Projects]![ProjectEndDate]) equal to the call and I am not good at CALLs
In query you can use:
Year([Projects]![ProjectEndDate]) = CurrentYear()
Year([Projects]![ProjectEndDate]) = Year(Date)