By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 455,657 Members | 1,760 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,657 IT Pros & Developers. It's quick & easy.

# Calculated Column in Subform Problem

 Expert 100+ P: 904 Going to post this real fast since I have to go out, so I hope I get all the pertenent info in the message. I have a subform and the results are based on a select query. Results are say like so (just a snippet of the results) Quarter Value Calculated 4 200 25 3 175 -5 2 180 30 1 150 0 Assume the first 2 columns are derived from my select query. The 3rd column is the one I need to figure out. It is a formula and just assume I need to subtract the value of Qtr4 from Qtr3 for row 1, subtract Qtr3 from Qtr2 for row 2, etc. I am drawing a blank how I can accomplish this on the subform. Any direction would be appreciated. cheers, Oct 9 '08 #1
Share this Question
19 Replies

 Expert 5K+ P: 8,685 You can create a Calculated Field within the Query comprising the Record Source for the Sub-Form. This Calculated Field would call a Public Function and pass to it the Values in the [Quarter] and [Value] Fields for each Record. Logic would be contained within the Function and it would return an Integer Value representing the difference between the Quarters. I had something like this in mind: Calculated Field in Query Expand|Select|Wrap|Line Numbers Difference:fCalcFormula([Quarter], [Value]) Function definition: Expand|Select|Wrap|Line Numbers Public Function fCalcFormula(bytQuarter As Byte, intValue As Integer) As Integer   ...logic here End Function P.S. - You stated that this was a snippet of code, which confused me, since there are only 4 Quarters in a Year. Are multiple Years involved? This wouold change the code logic. When I have time, I'll actually work on a simple template for you. Oct 10 '08 #2

 Expert 100+ P: 904 Yes multiple years. I will look at this over the weekend. Thanks Ade. cheers, You can create a Calculated Field within the Query comprising the Record Source for the Sub-Form. This Calculated Field would call a Public Function and pass to it the Values in the [Quarter] and [Value] Fields for each Record. Logic would be contained within the Function and it would return an Integer Value representing the difference between the Quarters. I had something like this in mind: Calculated Field in Query Expand|Select|Wrap|Line Numbers Difference:fCalcFormula([Quarter], [Value]) Function definition: Expand|Select|Wrap|Line Numbers Public Function fCalcFormula(bytQuarter As Byte, intValue As Integer) As Integer   ...logic here End Function P.S. - You stated that this was a snippet of code, which confused me, since there are only 4 Quarters in a Year. Are multiple Years involved? This wouold change the code logic. Oct 10 '08 #3

 Expert 5K+ P: 8,685 Yes multiple years. I will look at this over the weekend. Thanks Ade. cheers, tblMshmyob Expand|Select|Wrap|Line Numbers Quarter    Value 1           150 2           180 3           175 4           200 qryMshmyob Expand|Select|Wrap|Line Numbers SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference FROM tblMshmyob ORDER BY tblMshmyob.Quarter DESC; Function Definition Expand|Select|Wrap|Line Numbers Public Function fCalcDiff(bytQuarter As Byte, intValue As Integer) As Integer Dim intValPrevQuar As Integer   If bytQuarter = 1 Then   fCalcDiff = 0 Else   'Calculate the Value for the previous Quarter   intValPrevQuar = DLookup("[Value]", "tblMshMyob", "[Quarter] = " & bytQuarter - 1)   Select Case bytQuarter     Case 2, 3, 4       fCalcDiff = [intValue] - intValPrevQuar     End Select End If End Function OUTPUT Expand|Select|Wrap|Line Numbers Quarter    Value    Difference 4           200         25 3           175         -5 2           180         30 1           150          0 P.S. - Multiple Years are not taken into consideration. Oct 10 '08 #4

 Expert 100+ P: 904 Thanks Ade. This looks like it will work for me. There is more to it than years and quarters and the formula is also more complicated than subtracting but I am sure with your suggestion I can make it all work, since I have done it outside of the subform :). I was just drawing a blank on getting it to work inside the subform. Your idea looks like it will work. The fact that you used a global function is perfect since I am using a global function now for the calculation outside the subform. I let you know after the weekend. cheers, tblMshmyob Expand|Select|Wrap|Line Numbers Quarter    Value 1           150 2           180 3           175 4           200 qryMshmyob Expand|Select|Wrap|Line Numbers SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference FROM tblMshmyob ORDER BY tblMshmyob.Quarter DESC; Function Definition Expand|Select|Wrap|Line Numbers Public Function fCalcDiff(bytQuarter As Byte, intValue As Integer) As Integer Dim intValPrevQuar As Integer   If bytQuarter = 1 Then   fCalcDiff = 0 Else   'Calculate the Value for the previous Quarter   intValPrevQuar = DLookup("[Value]", "tblMshMyob", "[Quarter] = " & bytQuarter - 1)   Select Case bytQuarter     Case 2, 3, 4       fCalcDiff = [intValue] - intValPrevQuar     End Select End If End Function OUTPUT Expand|Select|Wrap|Line Numbers Quarter    Value    Difference 4           200         25 3           175         -5 2           180         30 1           150          0 P.S. - Multiple Years are not taken into consideration. Oct 10 '08 #5

 Expert Mod 15k+ P: 31,712 Thanks Ade. Just to clarify - you may have seen references to Ade in threads with ADezii involved. That's generally a reference to me (Abbreviation of Adrian in real life as ADezii knows). I can't say too much about another member's personal details, but I do know that Ade is not an abbreviation for ADezii. Oct 12 '08 #6

 Expert 5K+ P: 8,685 Just to clarify - you may have seen references to Ade in threads with ADezii involved. That's generally a reference to me (Abbreviation of Adrian in real life as ADezii knows). I can't say too much about another member's personal details, but I do know that Ade is not an abbreviation for ADezii. You mean I'm not Ade! Oct 12 '08 #7

 Expert Mod 15k+ P: 31,712 You KNOW I can never be as uncomplicated as that ADezii! Oct 13 '08 #8

 Expert 100+ P: 904 Sorry Ade and sorry ADe. cheers, Oct 13 '08 #9

 Expert 5K+ P: 8,685 Tou KNOW I can never be as uncomplicated as that ADezii! I was just laying back and waiting to see how long it would take you to catch on! (LOL). Oct 13 '08 #10

 Expert 5K+ P: 8,685 Sorry Ade and sorry ADe. cheers, No problem, mshmyob, NeoPa and I are known by many names, not all of them flattering! (LOL). Oct 13 '08 #11

 Expert Mod 15k+ P: 31,712 I was just laying back and waiting to see how long it would take you to catch on! (LOL). Of course there should have been quotes - but it only took a second scan to get the gist of what you were trying to say. Almost 5 seconds then :D Oct 13 '08 #12

 Expert 100+ P: 904 Finally got back to this problem. Is it possible to reference a recordset inside a module? I have tried the following (and variations) with no luck. Expand|Select|Wrap|Line Numbers Set rst = Forms!frmContactInfo!RORDetailsubform.Form.RecordsetClone   I have tried it outside the module and it works. Inside a module called by the function you indicated in the select query I get error # 7951 Expression has invalid reference to recodersetclone property. If I can get a recordset based on my select query it will make my calculation easier. cheers, Oct 16 '08 #13

 Expert 100+ P: 904 OK I modified my query like so Expand|Select|Wrap|Line Numbers SELECT tblRORDetail.RORYear, tblRORDetail.RORQtr, tblRORDetail.RORQtrMarket, tblRORDetail.RORQTRCredits, tblAccountType.AccountTypeName, tblRORDetail.RORQTRDebits, fCalcRORQtr(RORYear,RORQtr,RORQtrMarket,RORQtrCredits,RORQTRDebits) AS RORPeriod FROM tblAccountType INNER JOIN tblRORDetail ON tblAccountType.AccountTypeID=tblRORDetail.AccountTypeID WHERE tblRORDetail.ContactID=forms!frmContactInfo.txtClientID AND tblAccountType.AccountTypeID=forms!frmContactInfo.RORDetailsubform.form.cboSelectAccountType ORDER BY tblRORDetail.RORYear DESC , tblRORDetail.RORQtr DESC , tblAccountType.AccountTypeName;    What I notice is that it seems to call the function only twice. Once on the first row of the subform and on the last row of the subform. Any row in between does not get called. The function also only gets called when the value of cboSelectAccountType is = 1 (which is the first accounttype in the table tblAccountType). Any other value the function never gets called. I will keep looking at it to see where I messed up but if you spot anything please let me know. cheers, Oct 16 '08 #14

 Expert 2.5K+ P: 2,653 Obviously, seeing fCalcRORQtr() function code would help spot anything. :) Oct 16 '08 #15

 Expert 100+ P: 904 Maybe :) I figured something out though. If any value such as RORCredits or RORDebits for instance is null then the query actually doesn't seem to call the function. If I put in values of 0 or greater for these 2 fields everything works as it should. Therefore I will just set my constraints for the fields to be >= 0. cheers, Obviously, seeing fCalcRORQtr() function code would help spot anything. :) Oct 16 '08 #16

 Expert 2.5K+ P: 2,653 Maybe :) I figured something out though. If any value such as RORCredits or RORDebits for instance is null then the query actually doesn't seem to call the function. If I put in values of 0 or greater for these 2 fields everything works as it should. Therefore I will just set my constraints for the fields to be >= 0. cheers, IMHO, its a bit overkill to set table constraint for the sake of single peace of code not loving Nulls. :) Did you try to declare function arguments as Variant and handle Nulls inside function logic? Oct 16 '08 #17

 Expert 100+ P: 904 Good point Fish. I also do not like settings those types of constraints. In my module function I changed the dblCredit and dblDebit variables from double to variant and it works without the contraints. Thanks for the help guys. cheers, IMHO, its a bit overkill to set table constraint for the sake of single peace of code not loving Nulls. :) Did you try to declare function arguments as Variant and handle Nulls inside function logic? Oct 16 '08 #18

 Expert 2.5K+ P: 2,653 Not a problem. Good luck. Oct 16 '08 #19

 Expert 5K+ P: 8,685 Finally got back to this problem. Is it possible to reference a recordset inside a module? I have tried the following (and variations) with no luck. Expand|Select|Wrap|Line Numbers Set rst = Forms!frmContactInfo!RORDetailsubform.Form.RecordsetClone   I have tried it outside the module and it works. Inside a module called by the function you indicated in the select query I get error # 7951 Expression has invalid reference to recodersetclone property. If I can get a recordset based on my select query it will make my calculation easier. cheers, The code itself, assigning RecordsetClone to the Object Variable rst is sound ,as long as rst is declared with the proper Scope. Oct 16 '08 #20

### Post your reply

Sign in to post your reply or Sign up for a free account.