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
Bytes IT Community
+ 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

mshmyob
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
Share on Google+
19 Replies


ADezii
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:
  1. Calculated Field in Query
    Expand|Select|Wrap|Line Numbers
    1. Difference:fCalcFormula([Quarter], [Value])
  2. Function definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcFormula(bytQuarter As Byte, intValue As Integer) As Integer
    2.   ...logic here
    3. 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

mshmyob
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:
  1. Calculated Field in Query
    Expand|Select|Wrap|Line Numbers
    1. Difference:fCalcFormula([Quarter], [Value])
  2. Function definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcFormula(bytQuarter As Byte, intValue As Integer) As Integer
    2.   ...logic here
    3. 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

ADezii
Expert 5K+
P: 8,685
Yes multiple years. I will look at this over the weekend.

Thanks Ade.

cheers,
  1. tblMshmyob
    Expand|Select|Wrap|Line Numbers
    1. Quarter    Value
    2. 1           150
    3. 2           180
    4. 3           175
    5. 4           200
  2. qryMshmyob
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference
    2. FROM tblMshmyob
    3. ORDER BY tblMshmyob.Quarter DESC;
  3. Function Definition
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcDiff(bytQuarter As Byte, intValue As Integer) As Integer
    2. Dim intValPrevQuar As Integer
    3.  
    4. If bytQuarter = 1 Then
    5.   fCalcDiff = 0
    6. Else
    7.   'Calculate the Value for the previous Quarter
    8.   intValPrevQuar = DLookup("[Value]", "tblMshMyob", "[Quarter] = " & bytQuarter - 1)
    9.   Select Case bytQuarter
    10.     Case 2, 3, 4
    11.       fCalcDiff = [intValue] - intValPrevQuar
    12.     End Select
    13. End If
    14. End Function
  4. OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. Quarter    Value    Difference
    2. 4           200         25
    3. 3           175         -5
    4. 2           180         30
    5. 1           150          0
  5. P.S. - Multiple Years are not taken into consideration.
Oct 10 '08 #4

mshmyob
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,

  1. tblMshmyob
    Expand|Select|Wrap|Line Numbers
    1. Quarter    Value
    2. 1           150
    3. 2           180
    4. 3           175
    5. 4           200
  2. qryMshmyob
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference
    2. FROM tblMshmyob
    3. ORDER BY tblMshmyob.Quarter DESC;
  3. Function Definition
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcDiff(bytQuarter As Byte, intValue As Integer) As Integer
    2. Dim intValPrevQuar As Integer
    3.  
    4. If bytQuarter = 1 Then
    5.   fCalcDiff = 0
    6. Else
    7.   'Calculate the Value for the previous Quarter
    8.   intValPrevQuar = DLookup("[Value]", "tblMshMyob", "[Quarter] = " & bytQuarter - 1)
    9.   Select Case bytQuarter
    10.     Case 2, 3, 4
    11.       fCalcDiff = [intValue] - intValPrevQuar
    12.     End Select
    13. End If
    14. End Function
  4. OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. Quarter    Value    Difference
    2. 4           200         25
    3. 3           175         -5
    4. 2           180         30
    5. 1           150          0
  5. P.S. - Multiple Years are not taken into consideration.
Oct 10 '08 #5

NeoPa
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

ADezii
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

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

mshmyob
Expert 100+
P: 904
Sorry Ade and sorry ADe.

cheers,
Oct 13 '08 #9

ADezii
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

ADezii
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

NeoPa
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

mshmyob
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
  1. Set rst = Forms!frmContactInfo!RORDetailsubform.Form.RecordsetClone
  2.  
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

mshmyob
Expert 100+
P: 904
OK I modified my query like so

Expand|Select|Wrap|Line Numbers
  1. 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; 
  2.  
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

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

mshmyob
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

FishVal
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

mshmyob
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

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

ADezii
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
  1. Set rst = Forms!frmContactInfo!RORDetailsubform.Form.RecordsetClone
  2.  
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.