I am building a query that I would like to run every month for the latest data, without having to go into the back end (design or SQL view) of the query. My db uses a main entry form and requires the user to enter a four digit date, for example 0209 for February 2009.
My main table that the query is based on has fields labeled with the four digit time codes dating back to 2007 and running through 2009. Each month a new column of data is added. So for example right now, the user would enter 0409. I want the query to pull in the field 0409.
I have tried this:
SELECT TIME_CODE_REFERENCE_TABLE.fldTIME_CODE, Sum(SumEarnings2009.BankEarnings) AS SumOfBankEarnings, Sum([SumEarnings2009].[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]) AS Expr1
FROM (TIME_CODE_REFERENCE_TABLE INNER JOIN SumEarnings2009 ON TIME_CODE_REFERENCE_TABLE.fldTOP_CYCLE_DATE = SumEarnings2009.CycleDate) INNER JOIN masterdata ON SumEarnings2009.GroupAccount = masterdata.[CAS ACCT NUMBER]
GROUP BY TIME_CODE_REFERENCE_TABLE.fldTIME_CODE
HAVING (((TIME_CODE_REFERENCE_TABLE.fldTIME_CODE)=[Forms]![GEM to CMR ENTER DIFFERENCES FORM]![Time_Code_txt]));
or just putting Expr1: Sum([SumEarnings2009].[& Time_Code_txt &]) into the Field section in Design view.
Both don't work.