If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fiscal YTD Calculation Not Working Correctly
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! |
#2
|
|||
|
|||
Fiscal YTD Calculation Not Working Correctly
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!In itialContactDate))) 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!Ca seCloseDate))) AS CloseMonth, Format(DateAdd("m",6,[CaseCloseDate]),"m") AS CloseFiscalMth, Format(DateAdd("m",6,tblIHBSCloseSumm!CaseCloseDat e),"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? "KARL DEWEY" wrote: 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 "Walt" wrote: 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! |
#3
|
|||
|
|||
Fiscal YTD Calculation Not Working Correctly
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 "Walt" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|