I'm trying to set up a budgeting worksheet looking at both Trailing 12 data and Current Year to Date Info. I want it to work for any year in which the routine is activated from an access form which supplies the Current Year to Date as chosen by user.
I'm working in Access 2013.
The code snippet is:
' Populate tmpYTDActual
strSQL = ""
strSQL = strSQL & "INSERT INTO tmpYTDActual ( [Key], YTDActual ) "
strSQL = strSQL & "SELECT tmpActualHist.Key, Sum(tmpActualHist.ExpAmt) AS SumOfExpAmt "
strSQL = strSQL & "FROM tmpActualHist "
strSQL = strSQL & "WHERE (((tmpActualHist.PostingDate) >= DateSerial(DatePart('yyyy',Date()),1,1)AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "[/B]
strSQL = strSQL & "GROUP BY tmpActualHist.Key;"
DoCmd.RunSQL strSQL
strSQL = ""
The offending line seems to be:
"WHERE (((tmpActualHist.PostingDate) >= DateSerial(DatePart('yyyy',Date()),1,1)AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "
It returns Code 3075 Syntax Error Missing Operator
But the same code
Print DateSerial(DatePart("yyyy",Date()),1,1)
1/1/2019
returns the date I am looking for in the sql statement.
This probably is related to quote marks in some way, but I've tried everything I can think of and none of the combinations I've tried works.
Thanks.
Bill Ogden (wkosr)
I'm working in Access 2013.
The code snippet is:
' Populate tmpYTDActual
strSQL = ""
strSQL = strSQL & "INSERT INTO tmpYTDActual ( [Key], YTDActual ) "
strSQL = strSQL & "SELECT tmpActualHist.Key, Sum(tmpActualHist.ExpAmt) AS SumOfExpAmt "
strSQL = strSQL & "FROM tmpActualHist "
strSQL = strSQL & "WHERE (((tmpActualHist.PostingDate) >= DateSerial(DatePart('yyyy',Date()),1,1)AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "[/B]
strSQL = strSQL & "GROUP BY tmpActualHist.Key;"
DoCmd.RunSQL strSQL
strSQL = ""
The offending line seems to be:
"WHERE (((tmpActualHist.PostingDate) >= DateSerial(DatePart('yyyy',Date()),1,1)AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "
It returns Code 3075 Syntax Error Missing Operator
But the same code
Print DateSerial(DatePart("yyyy",Date()),1,1)
1/1/2019
returns the date I am looking for in the sql statement.
This probably is related to quote marks in some way, but I've tried everything I can think of and none of the combinations I've tried works.
Thanks.
Bill Ogden (wkosr)