Interpolating from a table

[Dave]

I have a table of tank volumes. The first
contains "inches" incremented by 1 inch. The remaining
fields are tank names. The data in the table corresponds
to the number of liters in the tank at that particular
inch level. Each tank is different and I do not have
mathmatical functions for the tanks.

I can write queries that will look up the number of liters
in the tank at each incremented inch. However, the
operators will enter half and quarter inch levels so I
need to interpolate between the two levels.

I can do it in Excel getting two values using ROUNDDOWN()
and then adding one, then extrapolating using the
difference . Unfortunately, these functions are not in
Access. Is there an easier way?

Tanks in advance

 

[Nikos Yannacopoulos]

Dave,

Paste the code below in a standard module:

Function get_volume(tank, level)

Dim db As DAO.Database

Dim rst As DAO.Recordset

strSQL = "SELECT fldLevel, " & fld & " FROM tblTableName"

strSQL = strSQL & " ORDER BY fldLevel"

Set db = CurrentDb()

Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst

llev = rst.Fields(0)

lvol = rst.Fields(1)

rst.MoveNext



If level < llev Then

MsgBox "Level below table range"

GoTo f_exit

End If

Do Until rst.EOF

ulev = rst.Fields(0)

uvol = rst.Fields(1)

If level <= ulev Then

get_volume = lvol + (uvol - lvol) * (level - llev) / (ulev - llev)

GoTo f_exit

End If

llev = rst.Fields(0)

lvol = rst.Fields(1)

rst.MoveNext

Loop

If level > llev Then

MsgBox "Level above table range"

End If



f_exit:

rst.Close

End Function



and change tblTableName to the name of the table holding the data, and
fldLevel to the name of the field holding the inch increments in that table.

You can then use function get_volume(tank, level) from anywhere in your
database (queries, forms, reports etc.), just like any Access built-in
function, to return the corresponding volume. The arguments used are: tank =
name of the field in the table for the tank in question, and level = inches
to calculate for.



HTH,

Nikos

 

[Dave]

Thanks Nikos,
It works great! First time using a function.. opens up
many new possibilities for my project.
Dave