How can I make this run faster?

[kutibo]

This function looks up values in the specified table, (either L1 or L05) column, (SURVIVE or EXPECT) and row. How can I make this run faster?
Function lookupBLDcurve(fldCurve, number)
Select Case fldCurve
Case "SL1" lookupBLDcurve = Nz(DLookup("SURVIVE", "L1", "fldAgeOfAvgLife = " & number))
Case "EL1"
lookupBLDcurve = Nz(DLookup("EXPECT", "L1", "fldAgeOfAvgLife = " & number))
Case "SL05"
lookupBLDcurve = Nz(DLookup("SURVIVE", "L05", "fldAgeOfAvgLife = " & number))
Case "EL05"
lookupBLDcurve = Nz(DLookup("EXPECT", "L05", "fldAgeOfAvgLife = " & number))
Case Else
lookupBLDcurve = -1
End Select
End Function

Thanks for your help.

 

[braindead2]

perhaps using sql
something like below ( you will need to include more error trapping but this should give you idea)
Function lookupBLDcurve(fldCurve, number)
dim fldname as string tblename as string
dim rst as recordset, strsql as string
select case Left(fldcurve,1)
case "E":fldname = "Expect"
case "S":fldname = "Survive"
Case Else
lookupBLDcurve = -1
End Select
tblname = mid(fldcurve,2)
strsql = select " & fldname & " from " & tblname & "where _
& " fldAgeOfAvgLife = " & number
set rst = currentdb.openrecordset(strsql)
rst.movefirst
lookupBLDcurve = rst.fields(fldname)
rst.close

good luck

 

[MichaelRed]

Certainly using the DLookUp function is one of the slower approaches to getting values. A straight SQL soloution may improve the response, however the overhead of instantiating hte db and rs may offest most of the savings if it is not done carerfully.

Some consideration needs to be give to the stratgeic plan for a problem like this.

If the lookup is to be accomplished quite often during a session, the dataset may (probably) would be placed in memory as one or more arrays of values. It 'looks like' a standard insurance mortality table, which usually has only a few hundred entries, so even the four variations noted here, the total is probably less than 1K records, each having only a few fields. these will easily fit into an array (or array of UDTs). Lookup from the arrays would probably be at least an order of magnitude quicker than the SQL apporach.

If hte lookup is seldom or done is used only occassionally for a given session of the program the SQL soloution may be the fastest.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over