Looking for syntax advice. I'm building the queries in the Query Designer. I have determined the current fiscal begin and end date from Date(Now) as named variables, and am trying to use the begin and end date variables from the first query in a subsequent query in a "between date and date" statement. I'm getting this error msg: query must contain at least one table or query.
SQL from my first query to determine the current fiscal begin and end date. This query is working:
Code:
SELECT IIf(Month(Now()) In (10,11,12),"10/1/" & Year(Now()),"10/1/" & Year(Now())-1) AS FFYBeginDate, IIf(Month(Now()) In (1,2,3,4,5,6,7,8,9),"9/30/" & Year(Now()),"9/30/" & Year(Now())+1) AS FFYEndDate;
The results for FFYBeginDate=10/1/2015, and the results for FFYEndDate=9/30/2016.
My subsequent query results in the error message mentioned above:
Code:
SELECT zpreqryFundsFFYCalc1.FFYBeginDate, zpreqryFundsFFYCalc1.FFYEndDate, FundsSubTable.UniqueID, FundsSubTable.Code, FundsSubTable.DateOfService, FundsSubTable.Amount, FundsSubTable.Note
FROM zpreqryFundsFFYCalc1, FundsSubTable
WHERE (((FundsSubTable.Code) In ("52","56")) AND ((FundsSubTable.DateOfService) Between [zpreqryFundsFYCalc1].[FFYBeginDate] And [zpreqryFundsFYCalc1].[FFYEndDate]))
ORDER BY FundsSubTable.Code, FundsSubTable.DateOfService DESC;
Thanks for your advice.