Calculating a number using values from an offset record

[zfind]

Hi everyone,

I'm writing a small function that will be used to calculate some revenue numbers based on a few conditions. One of the conditions is a delay period, in weeks.

It's stored in a table, but let's say it will be 4. I want my function to take a number from the current record, Number A and multiply it with Number B from an associated record 4 weeks behind it. Is there a simple function I can use in VBA to accomplish this, like an 'offset' perhaps? Someone mentioned that recordsets could work, but I'm not familiar with using that method.

Here's a daigram of what I want to happen:




Hopefully the above is descriptive enough, tell me if more info is needed.

Thanks!

 

[ajetrumpet]

Interesting problem zFind. I'm not sure, since there is no "offset" function in Access. However, you could do something like this (rs = recordset, x = long):

[I]open the object and navigate to the current record[/I]

  x = !numberB
    .move .absoluteposition - 4

[B]Function = x * !numberB[/B]

 

[zfind]

This one was a pickle but I ended up solving it pretty easily with dlookup:

DelayedAmount = Nz(DLookup("" & eco_gross & "", "tblRecords", "[WeekID]=" & DelayedWeek & " And [PartnersetID]=" & pid), 0)

Problem is, it's very slow.

 

[DCrake]

Try converting it into a function as follows:

DelayedAmount = Nz(DLookup("" & eco_gross & "", "tblRecords", "[WeekID]=" & DelayedWeek & " And [PartnersetID]=" & pid), 0)

Public Function GetDelayedAmount([COLOR="Red"]DelayedWeek [/COLOR]As Integer, [COLOR="SeaGreen"]PartSetID [/COLOR]As Integer) As Integer

Dim Rs As DAO.Recordset
Dim Amt As Intetger

Set Rs = CurrentDb.OpenRecordset("Select * From tblRecords Where WeekId=" & [COLOR="red"]DelayedWeek [/COLOR]& " And PartnersetID=" & [COLOR="seagreen"]PartSetID[/COLOR])

If Not Rs.EOF or Not Rs.BOF Then 
   Amt = Nz(Rs("eco_gross"),0)
   Rs.Close
Else
  Amt = 0
End If

Set Rs = Nothing

GetDelayedAmount = Amt

End Function

CodeMaster: