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

wkosr46

New member
Local time
Yesterday, 22:10
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

..forever waiting... waiting for jellybean!
Local time
Today, 10:10
Joined
May 7, 2009
Messages
19,169
"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
Local time
Yesterday, 22:10
Joined
Jan 23, 2006
Messages
15,364
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
Staff member
Local time
Yesterday, 19:10
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 02:10
Joined
Jan 14, 2017
Messages
18,186
Whichever expression you use, you need a space before AND in your code
 

wkosr46

New member
Local time
Yesterday, 22:10
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
Staff member
Local time
Yesterday, 19:10
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 02:10
Joined
Jan 14, 2017
Messages
18,186
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

Top Bottom