Calculated Column in Subform Problem

[mshmyob]

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,

[ADezii]

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
   

   Difference:fCalcFormula([Quarter], [Value])

2. Function definition:
   

   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.

[mshmyob]

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
   

   Difference:fCalcFormula([Quarter], [Value])

2. Function definition:
   

   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.

[ADezii]

Yes multiple years. I will look at this over the weekend.

Thanks Ade.

cheers,

1. tblMshmyob
   

   Quarter	Value
   1	       150
   2	       180
   3	       175
   4	       200

2. qryMshmyob
   

   SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference
   FROM tblMshmyob
   ORDER BY tblMshmyob.Quarter DESC;

3. Function Definition
   

   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

4. OUTPUT
   

   Quarter	Value	Difference
   4	       200	     25
   3	       175	     -5
   2	       180	     30
   1	       150	      0

5. P.S. - Multiple Years are not taken into consideration.

[mshmyob]

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
   

   Quarter	Value
   1	       150
   2	       180
   3	       175
   4	       200

2. qryMshmyob
   

   SELECT tblMshmyob.Quarter, tblMshmyob.Value, fCalcDiff([Quarter],[Value]) AS Difference
   FROM tblMshmyob
   ORDER BY tblMshmyob.Quarter DESC;

3. Function Definition
   

   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

4. OUTPUT
   

   Quarter	Value	Difference
   4	       200	     25
   3	       175	     -5
   2	       180	     30
   1	       150	      0

5. P.S. - Multiple Years are not taken into consideration.

[NeoPa]

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.

[ADezii]

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!

[NeoPa]

You KNOW I can never be as uncomplicated as that ADezii!

[mshmyob]

Sorry Ade and sorry ADe.

cheers,

[ADezii]

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).

[ADezii]

Sorry Ade and sorry ADe.

cheers,

No problem, mshmyob, NeoPa and I are known by many names, not all of them flattering! (LOL).

[NeoPa]

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

[mshmyob]

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.

[code=vb]
Set rst = Forms!frmContac tInfo!RORDetail subform.Form.Re cordsetClone
[/code]

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,

[mshmyob]

OK I modified my query like so

[code=vb]
SELECT tblRORDetail.RO RYear, tblRORDetail.RO RQtr, tblRORDetail.RO RQtrMarket, tblRORDetail.RO RQTRCredits, tblAccountType. AccountTypeName , tblRORDetail.RO RQTRDebits, fCalcRORQtr(ROR Year,RORQtr,ROR QtrMarket,RORQt rCredits,RORQTR Debits) AS RORPeriod FROM tblAccountType INNER JOIN tblRORDetail ON tblAccountType. AccountTypeID=t blRORDetail.Acc ountTypeID WHERE tblRORDetail.Co ntactID=forms!f rmContactInfo.t xtClientID AND tblAccountType. AccountTypeID=f orms!frmContact Info.RORDetails ubform.form.cbo SelectAccountTy pe ORDER BY tblRORDetail.RO RYear DESC , tblRORDetail.RO RQtr DESC , tblAccountType. AccountTypeName ;
[/code]

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 cboSelectAccoun tType 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,

[FishVal]

Obviously, seeing fCalcRORQtr() function code would help spot anything. :)