Use of DLookUp with interpolation???

[anb001]

In the attached sample DB I have a table, with "Ullage" and "M3" fields.

If I on a form use below code on textboxes, I can get the volume in M3, if I type in the Ullage.

Example code:

Me.txtVolumeM3 = Nz(DLookup("M3", "tblUllage", "Ullage='" & Me.txtUllage & "'"))

The issue I have is for each centimeter in the ullage, there is a corresponding M3. But that is not the case the other way!

If I type in a volume, how can I get the correct Ullage?

 

[Ranman256]

make another text box to do the reverse.

 

[anb001]

Ranman256,

If you look in the table attached, then it is not as simple. If I type in '50', then the DB needs to interpolate between 49,94 and 50,10, to find out which ullage should be returned!

 

[jdraw]

Do you have some guidelines regarding which or what calculation should be made?

Access won't guess. You have to give instructions for getting the result you want.

What exactly is the relationship between ullage and M3?

 

[MarkK]

I wouldn't use DLookup() at all. Use a query with a sort order, like . . .

Function GetUllageFromM3(M3 As Single) as Single
   Dim SQL as string

   SQL = _
      "SELECT Ullage " & _
      "FROM tblUllage " & _
      "WHERE M3 <= " & M3 & " " & _
      "ORDER BY M3"
   GetUllageFromM3 = CurrentDb.OpenRecordset(SQL).Fields(0).Value
End Function

 

[anb001]

Finally back online again

MarkK, thank you. I will try your suggestion as soon as I get back home again.