Year to Date

Glad we could get it working for you.
 
Code:
=DSum("Traffic", "tbl_Tickets", "[Ticket_Date] Between DateSerial(Year([B][COLOR=red]Date()[/COLOR][/B])-1, 10, 1) And DateSerial(Year([COLOR=red][B]Date()[/B][/COLOR]), 9, 30)")

Bob, not to put a bee in you bonnet... But what will happen if today is December 5???
In your setting you will get a static value for the months of October, november and December....

Instead I find it 'easier' to shift the 'financial year' to a 'proper year' using:
Dateadd("M",3,date())

In which case it would look something like:
Code:
=DSum("Traffic", "tbl_Tickets", "Year(Dateadd("M",3,date())) = Year(Dateadd("M",3,[Ticket_Date]))
 
never mind
 
I tried this code, but I am getting a syntax error.
=DSum("Traffic", "tbl_Tickets", "Year(Dateadd("M",3,date())) = Year(Dateadd("M",3,nz([Ticket_Date],Date())))
 
And if I am trying to gather year to date info from 10/01/ - 9/30, wouldn't I want to change the '3' to '-3'?
 
Syntax error simply means there is something wrong with the literal syntax.....
I am pretty sure if you spend 2 seconds really looking (a little) hard(er) you can find the error...

If you 'space' the code to match the brackets like so:
Code:
=DSum("Traffic", "tbl_Tickets"
               , "Year(Dateadd("M",3,date()
                              )
                      ) = Year(Dateadd("M",3,nz([Ticket_Date],Date()
                                               )
                                      )
                              )

You find that the first bracket of DSum is missing its closing bracket:
Code:
=DSum("Traffic", "tbl_Tickets"
               , "Year(Dateadd("M",3,date()
                              )
                      ) = Year(Dateadd("M",3,nz([Ticket_Date],Date()
                                               )
                                      )
                              )
     )

jzacharias said:
And if I am trying to gather year to date info from 10/01/ - 9/30, wouldn't I want to change the '3' to '-3'?
No you do not, basicaly you want to change Oct 1 to Jan 1 and change Sep 30 to Dec 30
As a result the "financial Year" will be the same, throughout the year....
 

Users who are viewing this thread

Back
Top Bottom