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;