Year to Date

[jzacharias]

How could I have a text box in a subform keep a sum of tickets written for a year to date (October 01-September 30)?

 

[ghudson]

Learn how to use the DSum() function.

 

[jzacharias]

I can get the sum, but I don't know how to change the "normal" year to date of January 1st through December 31st to October 1st through September 30th

 

[boblarson]

Something like this for the control source of the text box:

=DSum("YourFieldToSum", "TableToSumFrom", "[DateFieldNameHere] Between DateSerial(Year()-1, 10, 1) And DateSerial(Year(), 9, 30)")

 

[jzacharias]

I received an error in the text box.

 

[boblarson]

I received an error in the text box.

It would help if you provided the text of the error. We can't see what you see and I know I can't read minds.

Also, make sure the text box is not named the same as the field names which you are looking for.

 

[jzacharias]

Sorry..it just says #ERROR. The text box is just currently named text16

 

[jzacharias]

My field name to sum is "Traffic", table name is "tbl_Tickets", and date field is Ticket_Date

 

[boblarson]

Do your fields have the underscores in them or are they really spaces?

Oh, and did you include the

=

in the formula?

 

[jzacharias]

Underscore

 

[boblarson]

So you have this in the text box's control source:

=DSum("Traffic", "tbl_Tickets", "[Ticket_Date] Between DateSerial(Year()-1, 10, 1) And DateSerial(Year(), 9, 30)")

And Ticket_Date is a date/time datatype?

 

[jzacharias]

Correct. The only thing i did was change the format to display month/year on the form. Not sure if that would mess it up

 

[jzacharias]

Still receiving #Error

 

[boblarson]

How is your date stored? (what I mean is - mm/dd/yyyy or dd/mm/yyyy)

 

[jzacharias]

Stored as Month, Year (September,2010)

 

[boblarson]

Well, that would definitely not work with this then. You have to have a full date. But, I've been trying with a test database and I'm even having a problem in doing this.

I think I just found the problem - STUPID ME!!!!!!

=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)")

 

[jzacharias]

That is awesome. Thank you very much. Now I have to understand what is going on. I have looked through all the criteria in Access help and none of them show dateserial

 

[boblarson]

Go to Access VBA help for that. Go into the VBA window and then do a search from there.

 

[jzacharias]

A new set of problems now. I thought it would return the sum between those dates for the officer I was currently looking at in the form. It doesn't pull each officers sum, but the sum of all tickets for all officers.

 

[boblarson]

Okay, so you will need to use additional criteria. Since it is a name, you will need quotes too. Like this:

=DSum("Traffic", "tbl_Tickets", "[Ticket_Date] Between DateSerial(Year([COLOR=black]Date()[/COLOR])-1, 10, 1) And DateSerial(Year([COLOR=black]Date()[/COLOR]), 9, 30) [B][COLOR=red]And [Officer]=" & Chr(34) & [Officer] & Chr(34)[/COLOR][/B]),0)")