Fiscal YTD Calculation Not Working Correctly

[Guest]

Hello - I have a report that contains text boxes as calculated controls for
MTD and YTD totals. These text boxes perform record counts and increment the
totals for MTD and YTD by one each time the conditions in the expressions in
the text boxes (one for MTD and another for YTD) are met. The fiscal year is
July to June. The record source is a query that contains a field
(InitialContactDate) that contains a date for each record. For each date in
this field, other fields in this query (added by expressions) list the name
of the month (ContactMonth) (i.e. March), the fiscal year (ContactDateFY)
(i.e. 2007) and the number of the month in the fiscal year (ContactFiscalMth)
(i.e. 9...staying with March as the example illustrated here.). The user
selects the month and year to report on through combo boxes on a form. The
month combo box is bound to a two field table that has the month number in
one field and the name of the month in another. The combo box displays the
name of the month selected. The year combo box is bound to another table
that displays the year to select. Also on this form is a text box that
displays the month number automatically when the month name is selected. The
month number’s control source is the month number which is bound to the month
number field in the table previously described.

The expression to calculate the MTD count is working fine. Here is the
code...

=(Count(IIf([ContactMonth]=Forms!frmPeriod!Month And
[ContactDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

Note that the above expression counts the record if the month name and the
fiscal year number match.

Now here’s my problem. Here is the YTD code…

=Sum(Abs([ContactFiscalMth]<=Forms!frmPeriod!MonthNo And
[ContactDateFY]=Forms!frmPeriod!FiscalYear))

This expression says to count the record if the month number is less than or
equal to the month number automatically displayed in the form and if the year
match. The reason Sum was used with a nested Abs function is because I was
encountering the same problem with the Count and nested IIf function as used
in the MTD expression above. The problem I’m encountering is that all of
records in the query are being counted instead of the YTD total being
returned. To troubleshoot, if I get rid of the < part of the first condition
for the heck of it, I get the same MTD number which is expected. If I get
rid of the = part of expression, the count equals the total of all records
reduced by the amount of the records of the month being queried. The only
difference between the MTD expression and the YTD expression is the reference
to the month number (MonthNo). I’ve made sure the format of the number being
passed for the month number is a number and not a text. There’s no rhyme or
reason to why this is happening and it seems like such a simple issue
compared to the trouble that it is causing me.

Any help would be greatly appreciated to help me understand how to fix.
Thanks!

 

[Guest]

July 1, 2007 through June 30, 2008 would be FY08. It's a typical state
government fiscal year.

Here is the complete SQL query...

SELECT tblIHBSCloseSumm.ClientID, tblIHBSCloseSumm.LastName,
tblIHBSCloseSumm.FirstName, tblIHBSCloseSumm.InitialContactDate,
IIf(tblIHBSCloseSumm!InitialContactDate Is
Null,"",MonthName(DatePart("m",tblIHBSCloseSumm!InitialContactDate))) AS
ContactMonth, Val(Format(DateAdd("m",6,[InitialContactDate]),"m")) AS
ContactFiscalMth, Val(Format(DateAdd("m",6,[InitialContactDate]),"yyyy")) AS
ContactDateFY, tblIHBSCloseSumm.CaseCloseDate,
IIf(tblIHBSCloseSumm!CaseCloseDate Is
Null,"",MonthName(DatePart("m",tblIHBSCloseSumm!CaseCloseDate))) AS
CloseMonth, Format(DateAdd("m",6,[CaseCloseDate]),"m") AS CloseFiscalMth,
Format(DateAdd("m",6,tblIHBSCloseSumm!CaseCloseDate),"yyyy") AS CloseDateFY,
tblIHBSCloseSumm.CloseReason, ([CaseCloseDate]-[InitialContactDate])/7 AS LOS
FROM tblIHBSCloseSumm;

As you can see, the components for the FY determination are done in the
query. All the expressions in the text boxes do in the report is simply
count the records when the conditons are met. That's the only "data
calcuation" that's taking place.

Hope this helps and provides clarity. Any suggestions?

I did not get from your post what FY 1 July 2007 would be. Government fiscal
year begins in October so that 1 October 2007 will be FY08.

I suggest you use separate criteria for FY than for your data calculation.
For instance you have
=(Count(IIf([ContactMonth]=Forms!frmPeriod!Month And [ContactDateFY]
=Forms!frmPeriod!FiscalYear,1,Null)))

Use this for fiscal year --
DatePart("yyyy",DateAdd("m",-6,[YourDate]))
That is if FY2007 starts 1 July 07 and use +6 if 1 July 07 is FY2008.

Do your counting and data sorting separately.

I do not know what is supposed to be summed here as there is only
comparrison of the forms textboxes to fields - no data is extracted to add.
=Sum(Abs([ContactFiscalMth]<=Forms!frmPeriod!MonthNo And [ContactDateFY]
=Forms!frmPeriod!FiscalYear))

You might post your complete query SQL for someone to look at and make
suggestions.

--
KARL DEWEY
Build a little - Test a little

Hello - I have a report that contains text boxes as calculated controls for
MTD and YTD totals. These text boxes perform record counts and increment the
totals for MTD and YTD by one each time the conditions in the expressions in
the text boxes (one for MTD and another for YTD) are met. The fiscal year is
July to June. The record source is a query that contains a field
(InitialContactDate) that contains a date for each record. For each date in
this field, other fields in this query (added by expressions) list the name
of the month (ContactMonth) (i.e. March), the fiscal year (ContactDateFY)
(i.e. 2007) and the number of the month in the fiscal year (ContactFiscalMth)
(i.e. 9...staying with March as the example illustrated here.). The user
selects the month and year to report on through combo boxes on a form. The
month combo box is bound to a two field table that has the month number in
one field and the name of the month in another. The combo box displays the
name of the month selected. The year combo box is bound to another table
that displays the year to select. Also on this form is a text box that
displays the month number automatically when the month name is selected. The
month number’s control source is the month number which is bound to the month
number field in the table previously described.

The expression to calculate the MTD count is working fine. Here is the
code...

=(Count(IIf([ContactMonth]=Forms!frmPeriod!Month And
[ContactDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

Note that the above expression counts the record if the month name and the
fiscal year number match.

Now here’s my problem. Here is the YTD code…

=Sum(Abs([ContactFiscalMth]<=Forms!frmPeriod!MonthNo And
[ContactDateFY]=Forms!frmPeriod!FiscalYear))

This expression says to count the record if the month number is less than or
equal to the month number automatically displayed in the form and if the year
match. The reason Sum was used with a nested Abs function is because I was
encountering the same problem with the Count and nested IIf function as used
in the MTD expression above. The problem I’m encountering is that all of
records in the query are being counted instead of the YTD total being
returned. To troubleshoot, if I get rid of the < part of the first condition
for the heck of it, I get the same MTD number which is expected. If I get
rid of the = part of expression, the count equals the total of all records
reduced by the amount of the records of the month being queried. The only
difference between the MTD expression and the YTD expression is the reference
to the month number (MonthNo). I’ve made sure the format of the number being
passed for the month number is a number and not a text. There’s no rhyme or
reason to why this is happening and it seems like such a simple issue
compared to the trouble that it is causing me.

Any help would be greatly appreciated to help me understand how to fix.
Thanks!

 

[Guest]

I did not get from your post what FY 1 July 2007 would be. Government fiscal
year begins in October so that 1 October 2007 will be FY08.

I suggest you use separate criteria for FY than for your data calculation.
For instance you have
=(Count(IIf([ContactMonth]=Forms!frmPeriod!Month And [ContactDateFY]
=Forms!frmPeriod!FiscalYear,1,Null)))

Use this for fiscal year --
DatePart("yyyy",DateAdd("m",-6,[YourDate]))
That is if FY2007 starts 1 July 07 and use +6 if 1 July 07 is FY2008.

Do your counting and data sorting separately.

I do not know what is supposed to be summed here as there is only
comparrison of the forms textboxes to fields - no data is extracted to add.
=Sum(Abs([ContactFiscalMth]<=Forms!frmPeriod!MonthNo And [ContactDateFY]
=Forms!frmPeriod!FiscalYear))

You might post your complete query SQL for someone to look at and make
suggestions.

--
KARL DEWEY
Build a little - Test a little

Hello - I have a report that contains text boxes as calculated controls for
MTD and YTD totals. These text boxes perform record counts and increment the
totals for MTD and YTD by one each time the conditions in the expressions in
the text boxes (one for MTD and another for YTD) are met. The fiscal year is
July to June. The record source is a query that contains a field
(InitialContactDate) that contains a date for each record. For each date in
this field, other fields in this query (added by expressions) list the name
of the month (ContactMonth) (i.e. March), the fiscal year (ContactDateFY)
(i.e. 2007) and the number of the month in the fiscal year (ContactFiscalMth)
(i.e. 9...staying with March as the example illustrated here.). The user
selects the month and year to report on through combo boxes on a form. The
month combo box is bound to a two field table that has the month number in
one field and the name of the month in another. The combo box displays the
name of the month selected. The year combo box is bound to another table
that displays the year to select. Also on this form is a text box that
displays the month number automatically when the month name is selected. The
month number’s control source is the month number which is bound to the month
number field in the table previously described.

The expression to calculate the MTD count is working fine. Here is the
code...

=(Count(IIf([ContactMonth]=Forms!frmPeriod!Month And
[ContactDateFY]=Forms!frmPeriod!FiscalYear,1,Null)))

Note that the above expression counts the record if the month name and the
fiscal year number match.

Now here’s my problem. Here is the YTD code…

=Sum(Abs([ContactFiscalMth]<=Forms!frmPeriod!MonthNo And
[ContactDateFY]=Forms!frmPeriod!FiscalYear))

This expression says to count the record if the month number is less than or
equal to the month number automatically displayed in the form and if the year
match. The reason Sum was used with a nested Abs function is because I was
encountering the same problem with the Count and nested IIf function as used
in the MTD expression above. The problem I’m encountering is that all of
records in the query are being counted instead of the YTD total being
returned. To troubleshoot, if I get rid of the < part of the first condition
for the heck of it, I get the same MTD number which is expected. If I get
rid of the = part of expression, the count equals the total of all records
reduced by the amount of the records of the month being queried. The only
difference between the MTD expression and the YTD expression is the reference
to the month number (MonthNo). I’ve made sure the format of the number being
passed for the month number is a number and not a text. There’s no rhyme or
reason to why this is happening and it seems like such a simple issue
compared to the trouble that it is causing me.

Any help would be greatly appreciated to help me understand how to fix.
Thanks!