I think this should be simple, but I've been struggling for too long.
I have a form with a bound text box which I would like to return a value it has calculated. It calls on the table 'Training' which stores the variables Client, Standard and Date. Client is a string, standard is an integer and Date is in the format dd/mm/yyyy. I would like the box to return the sum of the Standard entries for this year.
So, if we've been out to see Client A last week and done one session and been out again today and done two, the table might look like:
Client A - 1 - 01/01/2017 <br/>
Client A - 1 - 12/01/2018 <br/>
Client A - 2 - 17/01/2018 <br/>
Client B - 1 - 15/01/2018
I'd like my box to return the value '3', ignoring Client A's training last year or the training this year of any other client.
I can get the box to correctly distinguish between clients, but not between years using this code:
=DSum("[Standard]","Training","[Client] = '" & [Client] & "'")
That selects the client from the training table, based on the client record currently selected in the form. It sums all the training that client has ever had - so in the example above returns '4'.
What I am struggling with is how to restrict the year. I've tried a number of variations on:
& "Year([TrainingDate])=#" & Year(Date())
but always get either Name? or Error. What am I doing wrong?
With thanks, Matt
Use:
=DSum("[Standard]","Training","[Client] = '" & [Client] & "' And Year([TrainingDate]) = Year(Date())")