Using DateSerial to get first day of current year. (1 Viewer)

wkosr46

New member
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)
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,620
"WHERE tmpActualHist.PostingDate >=#" & Format(DateSerial(Year(Date()),1,1), "mm\/dd\/yyyy") & "# AND tmpActualHist.PostingDate <= #" & Format([forms]![frmBudgetSetup]![CurYTD], "mm\/dd\/yyyy") & "#"
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,545
From techontheNet
DateSerial ( year, month, day )

I'd try

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

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
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:

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
Whichever expression you use, you need a space before AND in your code
 

wkosr46

New member
Joined
May 27, 2019
Messages
5
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.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
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.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,320
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 (Users: 0, Guests: 1)

Top Bottom