Using DateSerial to get first day of current year.

wkosr46

New member
Local time
Today, 01:00
Joined
May 27, 2019
Messages
5
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)
 
"WHERE tmpActualHist.PostingDate >=#" & Format(DateSerial(Year(Date()),1,1), "mm\/dd\/yyyy") & "# AND tmpActualHist.PostingDate <= #" & Format([forms]![frmBudgetSetup]![CurYTD], "mm\/dd\/yyyy") & "#"
 
From techontheNet
DateSerial ( year, month, day )

I'd try

"WHERE (((tmpActualHist.PostingDate) >= DateSerial(Year(Date),1,1) AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "
 
Hi Bill. Either suggestions will probably work, but here's another one you could also try:

"WHERE (((tmpActualHist.PostingDate) >= DateSerial(Eval(DatePart('yyyy',Date())),1,1)
AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "

Hope it helps...
 
Last edited:
Whichever expression you use, you need a space before AND in your code
 
From techontheNet
DateSerial ( year, month, day )

I'd try

"WHERE (((tmpActualHist.PostingDate) >= DateSerial(Year(Date),1,1) AND (((tmpActualHist.PostingDate) <= [forms]![frmBudgetSetup]![CurYTD])) "

Thanks. I received three suggestions and plan to try them all out. Under a little bit of time pressure I went with this one because it seemed to be the simplest. It worked after I added () after Date. "DateSerial(Year(Date(),1,1)"

I had to be away all day and was so pleasantly surprised to get a number of responses. Thanks to all.
 
Thanks. I received three suggestions and plan to try them all out. Under a little bit of time pressure I went with this one because it seemed to be the simplest. It worked after I added () after Date. "DateSerial(Year(Date(),1,1)"

I had to be away all day and was so pleasantly surprised to get a number of responses. Thanks to all.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
And another solution.
Here is a slightly shorter version of the code suggested by jdraw…
Code:
"WHERE tmpActualHist.PostingDate Between DateSerial(Year(Date()),1,1) AND [Forms]![frmBudgetSetup]![CurYTD] "
 

Users who are viewing this thread

Back
Top Bottom