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.

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,

[mshmyob]

OK I modified my query like so

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,

[FishVal]

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

[mshmyob]

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

[FishVal]

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?

[mshmyob]

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?

[FishVal]

Not a problem.
Good luck.

[ADezii]

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.

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.