Search code examples
ms-accessms-access-2010ms-access-2007criteria

Criteria in Query Design to equal this year's data

Background

  • My data field InvoiceDate looks like MM/DD/YYYY ex) 11/4/2017
  • User enters date parameter through Between [BeginDate] And [EndDate]
  • This Year sum field criteria is Year([InvoiceDate])=Year(Date())

Problem When User enter [BeginDate]=10/1/2017 And [EndDate]=10/31/2017, My This year's $ sum includes current month (November)'s data as well.

Goal I want to parse months, from January upto Between [BeginDate] And [EndDate]'s month of current year

Any help would be highly appreciated!

Update: I have tried

Year([InvoiceDate])=Year(Between [BeginDate] And [EndDate])<=Month(Between [BeginDate] And [EndDate])

But I'm getting ODBC failed error. is it because it's a long criteria?

Solution

  • Start and end dates in different years are possibly a problem, but this criteria string will always give you YTD for the year of the EndDate:

    [InvoiceDate] <= [EndDate] AND [InvoiceDate] >= "1/1/" & Year([EndDate])

    You could use BETWEEN if you like too:

    [InvoiceDate] BETWEEN "1/1/" & Year([EndDate]) AND [EndDate]