Remember value of last row in Query with VBA

[mordja]

Hi,

I have a query which matches up pairs of Ids from the same table and samefield based on certain criteria. Once a match has been found I can no longer consider those Ids for further pairs. To do this I need to know both of the ids from the prior row.

I have written a module (only stores one Id will duplicate when working for other Id)

Option Compare Database

Global lastRecalcID As Integer

Public Function getRecalcID(ByVal recalcId As Integer)

getRecalcID = lastRecalcID

lastRecalcID = recalcId
End Function

to return the last value, then store the new value. Works fine when testing in the VBE but when I use it in a query
the results are

lastRecalc RecalcID
16 16
17 17
16 16
17 17

instead of

lastRecalc RecalcID
0 16
16 17
17 16
16 17

The lastvalue and the current value are always the same, and no zero is returned for the first row when I assume lastRecalcID would be uninitialised, any suggestions.

Thanks

Mordja

 

[Eupher]

Can you post the SQL of the query?

Ken S.

 

[mordja]

Eupher,

After further investigation I realised that the error was a result of the join and that the module worked as it should.
And by using the function on above the results I got closer to where I wanted.

SELECT DISTINCT CANCELL.adjustmentId AS CancelID, RECALC.adjustmentId AS RecalcID, CANCELL.companyCode, CANCELL.[Taps account] AS CancelTaps, CANCELL.billReference, RECALC.collateralCurrency, CANCELL.clientCode, CANCELL.validfromdate, Abs(CANCELL!SumOfusdpnlamount+RECALC!SumOfusdpnlamount) AS Diff, CANCELL.SumOfusdpnlamount AS CancelUSD, RECALC.SumOfusdpnlamount AS RecalcUSD, IIf(Left(recalc!eventReference,Len(recalc!eventreference)-1)=Left(cancell!eventReference,Len(cancell!eventreference)-1),"Same","Different") AS EventReferenceSame, Format([StartDate],"dd-mmm-yy") AS [From], Format([EndDate],"dd-mmm-yy") AS [To], RECALC.borrowLoanPledgeReceipt, RECALC.[Taps account] AS RecalcTaps
FROM BillingAdjustments AS CANCELL INNER JOIN BillingAdjustments AS RECALC ON (CANCELL.Flag = RECALC.Flag) AND (CANCELL.collateralCurrency = RECALC.collateralCurrency) AND (CANCELL.companyCode = RECALC.companyCode) AND (CANCELL.billReference = RECALC.billReference) AND (CANCELL.validfromdate = RECALC.validfromdate) AND (CANCELL.clientCode = RECALC.clientCode) AND (CANCELL.borrowLoanPledgeReceipt = RECALC.borrowLoanPledgeReceipt)
GROUP BY CANCELL.adjustmentId, RECALC.adjustmentId, CANCELL.companyCode, CANCELL.[Taps account], CANCELL.billReference, RECALC.collateralCurrency, CANCELL.clientCode, CANCELL.validfromdate, Abs(CANCELL!SumOfusdpnlamount+RECALC!SumOfusdpnlamount), CANCELL.SumOfusdpnlamount, RECALC.SumOfusdpnlamount, IIf(Left(recalc!eventReference,Len(recalc!eventreference)-1)=Left(cancell!eventReference,Len(cancell!eventreference)-1),"Same","Different"), Format([StartDate],"dd-mmm-yy"), Format([EndDate],"dd-mmm-yy"), RECALC.borrowLoanPledgeReceipt, RECALC.[Taps account], Left([recalc]![comments],6), Left([cancell]![comments],7)
HAVING (((CANCELL.validfromdate)>=[StartDate] And (CANCELL.validfromdate)<=[EndDate]) AND ((Left([recalc]![comments],6))="RECALC") AND ((Left([cancell]![comments],7))="CANCELL") AND ((Abs([CANCELL]![SumOfusdpnlamount]+[RECALC]![SumOfusdpnlamount]))<20000)) OR (((Left([recalc]![comments],6))="RECALC") AND ((Left([cancell]![comments],7))="CANCELL") AND ((Abs([CANCELL]![SumOfusdpnlamount]+[RECALC]![SumOfusdpnlamount]))<20000));

Basically I have a table of adjustments, adjustments can be different types. I am interested in two types CANCELL and RECALC. A CANCELL should, but doesnt always, have a corresponding RECALC and vice versa. Im trying to match them with the above join criteria, and that the absolute difference in their USD is < 20k.
The above works fine but does not eliminate the case of there being say four adjustments, 2 CANCELLS and 2 RECALC adjustments with the same linking criteria which would return 4 pairs as opposed to the correct number 2. As the linking criteria is the same for these four adjustments it does not matter which are paired.

So to stop this happening I have to keep a list of adjustmentIds that are already in a pair and each row check that ids of the pair returned have not already been used. I Hope this makes sense. In hindsight the above module will not work anyways as I need to store all IDs which have been paired as opposed to the last two.

Suggestions.

Thanks

Mordja