Year to date information

[RichardMGreen]

Hi all

I've got some queries that run from January to December for year to date information.
I need to change them so they run from April to March.

Does anyone know how I would alter the query where statement to do that?

Here's an example where/having statement:-
HAVING (((ext_Contacts.formOfContact)<>"Telephone" And (ext_Contacts.formOfContact)<>"Email" And (ext_Contacts.formOfContact)<>"Letter") AND ((Year([contactDatetime]))=[Year]) AND ((Month([contactDatetime]))<=[Month Number]));

They are all built in a similar fashion so I just need a pointer in the right direction.

 

[dk]

Following on from xenou's post, here is one way that should replicate what your UDF was doing. Replace this:

get_year_start([End_Date])


With this:

DateSerial(Year([end_date])+IIf(Month([End_Date])<4,-1,0),4,1)

Also, as a side note it is normally better to use WHERE rather than HAVING. HAVING means aggregate everything and then filter, WHERE means filter then aggregate. It's quicker to aggregate a small number of records than a large number. Suggested SQL might be (not tested but should be ok):

SELECT ea!ClientId & ea!IADate AS [Distinct], ea.ClientId, Count(ea.ClientId) AS CountOfClientId, ea.IADate
FROM ext_Assessments ea
WHERE ea.IADate Between DateSerial(Year([end_date])+IIf(Month([End_Date])<4,-1,0),4,1) And [End_Date]
GROUP BY ea!ClientId & ea!IADate, ea.ClientId, ea.IADate

ORDER BY ea.IADate;

HTH
DK

 

[RichardMGreen]

It looks like HAVING is done automatically in the query builder unless I'm doing something stupid (which is entirely possible!!).

Thanks for the info using dateserial, I'll give it a go and see how I get on.

 

[RichardMGreen]

Looks like dateserial did the trick thanks. Saves me having to pass two parameters as well (I was working out the start date in Excel and passing it as a parameter).

 

[xenou]

It looks like HAVING is done automatically in the query builder unless I'm doing something stupid (which is entirely possible!!).

Thanks for the info using dateserial, I'll give it a go and see how I get on.

This is the default in the query builder when you add a criteria to a grouped query. But you can choose Where as the group by type (note: this also by default unchecks the box to show the field in the query results - if you need the field to be shown in the results then you check it again).

ξ