asked on Calculating a YTD value in MS ACCESS
I am trying to calculate a YTD value on a Subform (Datasheet View).
I have two tables, tblPaychecks and tblDeductions. tblDeductions is a child of tblPaychecks... tblDeductions.Paycheck_ID = tblPaychecks.ID
I am trying to display a form that shows the Paycheck details on top, and at the bottom a Datasheet view of all the deductions on the check being viewed. So far, this portion all works perfectly. The problem is, I also would like to add a calculated column to the Deductions Subform that shows a sum of all the deductions for the specified "deduction type" in the current fiscal year. I've tried using Domain based lookups, but ended up hitting a "string size limit" when defineing the ControlSource code. I then though of trying to build the code using VBA, but because of the way Form Events fire, i can't seem to get the values I need.
Attached is an image of my form with sample data. For each "Deduction" row I need to calculate the YTD for the Fiscal and Calendar year. The code should be the same except for the "Year Range" filter, so if I can get one to work, the other should be the same logic.
![Screenshot of Parent/Child Form]()
Anyone have any suggestions?
I have two tables, tblPaychecks and tblDeductions. tblDeductions is a child of tblPaychecks... tblDeductions.Paycheck_ID = tblPaychecks.ID
I am trying to display a form that shows the Paycheck details on top, and at the bottom a Datasheet view of all the deductions on the check being viewed. So far, this portion all works perfectly. The problem is, I also would like to add a calculated column to the Deductions Subform that shows a sum of all the deductions for the specified "deduction type" in the current fiscal year. I've tried using Domain based lookups, but ended up hitting a "string size limit" when defineing the ControlSource code. I then though of trying to build the code using VBA, but because of the way Form Events fire, i can't seem to get the values I need.
Attached is an image of my form with sample data. For each "Deduction" row I need to calculate the YTD for the Fiscal and Calendar year. The code should be the same except for the "Year Range" filter, so if I can get one to work, the other should be the same logic.
Anyone have any suggestions?
Microsoft AccessMicrosoft Office
Last Comment
Ben Santiardo
<I've tried using Domain based lookups, but ended up hitting a "string size limit" when defining the ControlSource code.>
Can you post what you were using?
A simple dsum should do the job as the controlsource for the YTD textbox:
Something roughly like this:
=DSum("Amount","YourTable"
Here is a sample:
(You will get an error on the new record at the bottom, which you can hide by setting the Allow Additions property of the subform to NO.
JeffCoachman
Database18.mdb
Can you post what you were using?
A simple dsum should do the job as the controlsource for the YTD textbox:
Something roughly like this:
=DSum("Amount","YourTable"
Here is a sample:
(You will get an error on the new record at the bottom, which you can hide by setting the Allow Additions property of the subform to NO.
JeffCoachman
Database18.mdb
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
1. You never posted this mysterious "mega" control source you keep speaking of...?
2. You never commented on my sample. AFAICT it does as you requested.
<in order for each row of a Datasheet to calculate a value to place in it's corresponding textbox, it has to be done as code in the "ControlSource" of said object. >
...Again, my sample did this in the controlsource directly...
3. <So instead what I did was compile the "ControlSource" code (using VBA) in as a compressed format>
Can to elaborate on this?
I don't know of a way to "Compile" a controlsource into a "compressed format".
It sounds like you simply set the control source of a control via VBA, However, this is not technically the same as "Compiling".
So I am not quite sure if your terminology is correct.
JeffCoachman
2. You never commented on my sample. AFAICT it does as you requested.
<in order for each row of a Datasheet to calculate a value to place in it's corresponding textbox, it has to be done as code in the "ControlSource" of said object. >
...Again, my sample did this in the controlsource directly...
3. <So instead what I did was compile the "ControlSource" code (using VBA) in as a compressed format>
Can to elaborate on this?
I don't know of a way to "Compile" a controlsource into a "compressed format".
It sounds like you simply set the control source of a control via VBA, However, this is not technically the same as "Compiling".
So I am not quite sure if your terminology is correct.
JeffCoachman
ASKER
@boag2000(JeffCoachman):
1. You never posted this mysterious "mega" control source you keep speaking of...?
Like I said in my post "I've tried numerous variations and no longer have the code that was being truncated" Suffice to say, ACCESS specifically said it was too long. At this point in time does it really matter what I wrote originally?
2. You never commented on my sample. AFAICT it does as you requested.
<in order for each row of a Datasheet to calculate a value to place in it's corresponding textbox, it has to be done as code in the "ControlSource" of said object. >
...Again, my sample did this in the controlsource directly...
I came across my solution before I saw your posted response. When I posted my response I as of yet didn't have a chance to look at your MDB. I was simply posting my results so far. I have since D/Led and looked at your MDB. Your code basically is identical to what I was already trying, however, because I need to calculate dynamically for Fiscal & Calandar year based on the CheckDate found in the PARENT record, the ControlSource code get's too long. There needed to be additional embedded lookup commands and such to determine the correct Date-Range to filter on.
3. <So instead what I did was compile the "ControlSource" code (using VBA) in as a compressed format>
Can to elaborate on this?
I don't know of a way to "Compile" a controlsource into a "compressed format".
It sounds like you simply set the control source of a control via VBA, However, this is not technically the same as "Compiling".
So I am not quite sure if your terminology is correct.
By "compile" I mean that I used VBA (as explained) to condense all the additional ControlSource code that I needed to extract the correct Records for DSUM. Instead of having additional Lookup commands (etc...), I processed all the "additional" components that I was able to beforehand, then I just inserted the correct filter items into the needed ControlSource DSUM command. I was also forced to create a Query that joined Child & Parent records so I could further shrink the final DSUM command text. That is what I mean by "compile" into a "compressed" format. This way I basically have the same exact code, but i've pre-calculated the portions that I can, so as to shorten the length of the final code. IMHO, I think my terminology is accurate. The act of gathering information/data and putting it together to produce something is by definition "compiling".
Here is the code I ultimately came up with:
1. You never posted this mysterious "mega" control source you keep speaking of...?
Like I said in my post "I've tried numerous variations and no longer have the code that was being truncated" Suffice to say, ACCESS specifically said it was too long. At this point in time does it really matter what I wrote originally?
2. You never commented on my sample. AFAICT it does as you requested.
<in order for each row of a Datasheet to calculate a value to place in it's corresponding textbox, it has to be done as code in the "ControlSource" of said object. >
...Again, my sample did this in the controlsource directly...
I came across my solution before I saw your posted response. When I posted my response I as of yet didn't have a chance to look at your MDB. I was simply posting my results so far. I have since D/Led and looked at your MDB. Your code basically is identical to what I was already trying, however, because I need to calculate dynamically for Fiscal & Calandar year based on the CheckDate found in the PARENT record, the ControlSource code get's too long. There needed to be additional embedded lookup commands and such to determine the correct Date-Range to filter on.
3. <So instead what I did was compile the "ControlSource" code (using VBA) in as a compressed format>
Can to elaborate on this?
I don't know of a way to "Compile" a controlsource into a "compressed format".
It sounds like you simply set the control source of a control via VBA, However, this is not technically the same as "Compiling".
So I am not quite sure if your terminology is correct.
By "compile" I mean that I used VBA (as explained) to condense all the additional ControlSource code that I needed to extract the correct Records for DSUM. Instead of having additional Lookup commands (etc...), I processed all the "additional" components that I was able to beforehand, then I just inserted the correct filter items into the needed ControlSource DSUM command. I was also forced to create a Query that joined Child & Parent records so I could further shrink the final DSUM command text. That is what I mean by "compile" into a "compressed" format. This way I basically have the same exact code, but i've pre-calculated the portions that I can, so as to shorten the length of the final code. IMHO, I think my terminology is accurate. The act of gathering information/data and putting it together to produce something is by definition "compiling".
Here is the code I ultimately came up with:
Private Sub Form_Load()
'************************************************************************
' Procedure/Function: Form_Load()
' Author: Ben Santiago
' Created On: 07/13/2011
' Description:
' Initialize form.
'************************************************************************
'***************************************
' Initialize Variables
'***************************************
Dim objTemplate As Recordset
Dim strSQL As String
Dim dtFiscalStart As Date
Dim dtFiscalEnd As Date
Dim dtCalendarStart As Date
Dim dtCalendarEnd As Date
Dim dtCurrentCheckDate As Date
'***************************************
' Retrieve Start & End Dates for Fiscal & Calendar Years
'***************************************
dtCurrentCheckDate = [Forms]![Paycheck Details]![CheckDate]
dtFiscalStart = GetYearRange(Fiscal, RangeStart, dtCurrentCheckDate)
dtFiscalEnd = GetYearRange(Fiscal, RangeEnd, dtCurrentCheckDate)
dtCalendarStart = GetYearRange(Calendar, RangeStart, dtCurrentCheckDate)
dtCalendarEnd = GetYearRange(Calendar, RangeEnd, dtCurrentCheckDate)
'***************************************
' Compile & Set "ControlSource" Code For FiscalYTD Object
'***************************************
strSQL = "Description = '""" & " & [Forms]![Paycheck Details]![Deductions].[Form]![Description] & ""' AND " & _
"CheckDate BETWEEN #" & dtFiscalStart & "# AND #" & dtCurrentCheckDate & "#"
Me.FiscalYTD.ControlSource = "=DSum(""Amount"",""qryDeductions_All"",""" & strSQL & """)"
'***************************************
' Compile & Set "ControlSource" Code For CalendarYTD Object
'***************************************
strSQL = "Description = '""" & " & [Forms]![Paycheck Details]![Deductions].[Form]![Description] & ""' AND " & _
"CheckDate BETWEEN #" & dtCalendarStart & "# AND #" & dtCurrentCheckDate & "#"
Me.CalendarYTD.ControlSource = "=DSum(""Amount"",""qryDeductions_All"",""" & strSQL & """)"
'***************************************
' Calculate & Display Check "Reconciliation" Data
'***************************************
Call BalanceCheck
End Sub
OK,
Thanks for the clarification.
;-)
Thanks for the clarification.
;-)
ASKER
I found my own solution.
It is possible to calculate YTD using SQL and queries, but it is very time consuming. From my experience the quickest way is using VBA to calculate the YTD.
I do this by:
Create a 'Crosstab query' where your criteria is columns, the month/period is the row and the amount is the first(value).
This way you can use VBA to run through each field and add up all the values for the time period.
This can also be used for QTD (Code needs changing though)
You can either run this as a batch operation, or on the fly for the On Current Event where it calculates it for the selected Paycheck and store it in tblDeductions. (So you would need to actively change the SQL that is used in the RS).
An example of the VB is below, but without a copy of the access DB (put dummy data in and remove logo's etc), I can't do it exactly.
Hope this Helps.
Open in new window