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.

 

[Trevor G]

Hi Richard,

Take a look at this sample as it is using DatePart

http://www.eggheadcafe.com/microsoft/Access-Queries/31896328/fiscal-year-to-date.aspx

CurrentFiscalYear Year(DateAdd("m",6,[YourDateField]))Month

John Spencer posted on Thursday, March 27, 2008 12:00 PM

<!---->
<TABLE border=0 cellSpacing=0 cellPadding=2 width="100%"><TBODY><TR><TD align=left>
CurrentFiscalYear<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Year(DateAdd("m",6,[YourDateField]))<o></o>
Month<o></o>
Month([YourDateField])<o></o>
FiscalMonthNumber<o></o>
Month(DateAdd("m",6,[YourDateField]))<o></o>
<o></o>
Field: FiscalMonthNumber: Month(DateAdd("m",6,[YourDateField]))<o></o>
Order By: Ascending<o></o>
Criteria: Between 1 and Month(DateAdd("m",6, Date())<o></o>
<o></o>
Field: FiscalYear: Year(DateAdd("m",6,[YourDateField]))<o></o>
Criteria: Between 2003 and 2007<o></o>
<o></o>
You can choose whether or not to show/hide the calculated field


</TD></TR></TBODY></TABLE>

 

[RichardMGreen]

Hi Trevor - Thanks for the link. That's for a Crystal Reports field. Do you know how I would convert it to Access?

 

[Trevor G]

What about this one then.

I will test it but it is from Microsoft so should work.

http://support.microsoft.com/kb/210249

 

[RichardMGreen]

Basically, I'll be feeding two parameters to the queries (reporting month and year) and I want to get the fiscal reporting information (from the previous April) up to and including that month/year.

 

[RichardMGreen]

It's OK, I've sorted it
I'll need to change about half my queries, but this works out the the start of the financial year

Function get_year_start(indate) As Date
    inmonth = Month(indate)
    inyear = Year(indate)
    If inmonth < 4 Then inyear = inyear - 1
    get_year_start = DateValue("01/04" & "/" & inyear)
End Function

 

[RichardMGreen]

Here's a bit of an oddity.
If I use this function with one date it works fine, if I change the date to something else, it doesn't.

Function get_year_start(indate As Date) As Date
    inmonth = Month(indate)
    inyear = Year(indate)
    If inmonth < 4 Then inyear = inyear - 1
    get_year_start = DateValue("01/04" & "/" & inyear)
End Function

For example, if use the date 31/08/2011 the function quite happily returns 01/04/2011 as the start date (which is correct for my purposes) and gives me 954 records.
However, if I use 31/06/2011 I get "The expression is typed incorrectly or is too complex to be evaluated" as an error.

This is the SQL for the query

SELECT ext_Assessments!ClientId & ext_Assessments!IADate AS [Distinct], ext_Assessments.ClientId, Count(ext_Assessments.ClientId) AS CountOfClientId, ext_Assessments.IADate
FROM ext_Assessments
GROUP BY ext_Assessments!ClientId & ext_Assessments!IADate, ext_Assessments.ClientId, ext_Assessments.IADate
HAVING (((ext_Assessments.IADate) Between get_year_start([End_Date]) And [End_Date]))
ORDER BY ext_Assessments.IADate;

Anyone any ideas why changing the date causes such a problem?

 

[RichardMGreen]

Ignore this, I'm going senile.
June only has 30 days!!!

 

[RichardMGreen]

Slight problem with the UDF above.
I'm using ADO to connect to the database and feeding parameters to the queries as necessary.

When I try to run the parameters, it's giving me an error saying that the function I've built is undefined.

Is there any way round this?

 

[xenou]

You can't use UDF's with ADO. There are "workarounds" - probably hundreds of them. It all depends on how you like to handle things. For starters, you could just try writing the query without a UDF.

Note for future reference: NZ() doesn't work with ADO either, so avoid that also.
ξ