Member Avatar for ChrisBuchanan
ChrisBuchanan 0 Newbie Poster

I have inherited the following query, which works:

SELECT [CM Database].[Age Range], Count([CM Database].[Age Range]) AS [Count], Format([Date Contacted],"yyyy") AS [Year]
FROM [CM Database]
GROUP BY [CM Database].[Age Range], Format([Date Contacted],"yyyy")
HAVING ((([CM Database].[Age Range]) Is Not Null) AND ((Format([Date Contacted],"yyyy"))=[Year]));

My client has asked for a Financial Year based summary of the above, which is not unreasonable.

I therefore added a query field to calculate the financial year change from 1st April, and format it:

Year([Date Contacted])-IIf([Date Contacted]<DateSerial(Year([Date Contacted]),4,1),1,0) AS FinYr, [FinYr] & "/" & Right(([FinYr]+1),2) AS [Financial Year]

The intention was then to group categorisations by financial year. But I get Type Mismatch messages, and/ or it accuses me of not including the [Date contacted] field. Am I trying to do something impossible?

Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 1 Contributor
  • 0 Replies
  • 42 Views
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.