Year to Date

[jzacharias]

Would you have to specify the tbl_Deputy where the last, first name is stored or can I used the "Deputy_ID" from the tickets table?

 

[boblarson]

Would you have to specify the tbl_Deputy where the last, first name is stored or can I used the "Deputy_ID" from the tickets table?

You would have to use the ID field if you are using the table in the DSum. You could create a query which has the ticket info and the name to use in the query and then refer to the query in the DSum instead of the table name. But you will need to have the criteria field in the recordsource of the form, whichever you use. I hope that makes sense.

If you go with the Deputy_ID (and if it is numeric) you won't need the CHR(34) parts added on.

 

[jzacharias]

I will try the Deputy_ID. What does the CHR(34) do?

 

[boblarson]

Chr(34) is a double quote " which is not needed if the field is numeric.

 

[jzacharias]

Ok. So i would replace both [officer] with Deputy_ID?

 

[boblarson]

Ok. So i would replace both [officer] with Deputy_ID?

Yep, so the final would look like:

=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 [Deputy_ID]=" & [Deputy_ID][/COLOR][/B]),0)")

 

[jzacharias]

My table structure is as follows:

tbl_Deputy
Deputy_ID
Body_Number
Last
First

tbl_Tickets
Ticket_ID
Ticket_Date
Traffic
Non_Traffic
DUI
Deputy_ID

relationship between the two

 

[boblarson]

The last code should do it for you (as long as Deputy_ID is in the recordsource for your form).

 

[boblarson]

Oh, and actually I don't think you want to use DSum. But I think you want DCount instead.

 

[jzacharias]

I definitely need to have a sum of all tickets for each deputy for the year to date. I thought count actually counts the number of records. Sorry, but I am really new to this

 

[boblarson]

What does TRAFFIC store? Is it a checkbox or is it actually the number? I may be wrong about the DCount as I started looking I was thinking it might be a checkbox instead of a number. If a number then yes, DSUM would be the one you want.

 

[jzacharias]

It is a number. Every month, the Sergeant will enter the total number of traffic tickets written for each Deputy. I thought it would be nice to have a text box on the form with a year to date total for each deputy. The last expression had a popup saying the expression you entered contains invalid syntax or you need to enclose your text data in quotes. The quotes are there, unless it needs more

 

[jzacharias]

Let me know if you got it

 

[boblarson]

Let me know if you got it

It isn't here. Did you make sure to run Compact and Repair first and then ZIP the file? It has to be at, or under, 2Mb for it to upload.

 

[jzacharias]

Yes. It was 27k

 

[boblarson]

Another common mistake when uploading is that you select the file and then close the form but you need to click the UPLOAD button first and then a link will appear just below the text box where the file path and name were for the selected file.

Once that link shows up then it means the form has it ready, but if you don't click the upload button it won't add it to the post.

 

[jzacharias]

Let me try again

 

[jzacharias]

I think it worked this time

 

[boblarson]

Okay, I got to see that you had it slightly wrong - This should work for you (it did for me):

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

 

[jzacharias]

That is really cool. Thank you very much.