Is this an efficient way to accomplish this?

[GBalcom]

If I was in Excel, I would simply use a vlookup set to true, but I couldn't find a way to accomplish this in access, short of the function below.

In short, attached is the table it's looking at.

Public Function ConThreshold(x As Currency)

'set up the dims
    Dim limit1 As Currency
    Dim limit2 As Currency
    Dim limit3 As Currency
    Dim limit4 As Currency
    Dim limit5 As Currency
    Dim limit6 As Currency
    Dim RowID As Integer
    

'Set the values
    limit1 = DLookup("JobSize", "tblContributionValues", "ID = 1")
    limit2 = DLookup("JobSize", "tblContributionValues", "ID = 2")
    limit3 = DLookup("JobSize", "tblContributionValues", "ID = 3")
    limit4 = DLookup("JobSize", "tblContributionValues", "ID = 4")
    limit5 = DLookup("JobSize", "tblContributionValues", "ID = 5")
    
'Figure out the Contribution Threshold for the given job
    If x >= limit1 And x < limit2 Then
        RowID = 1
    End If
    
    If x >= limit2 And x < limit3 Then
        RowID = 2
    End If
    
    If x >= limit3 And x < limit4 Then
        RowID = 3
    End If
    
    If x >= limit4 And x < limit5 Then
        RowID = 4
    End If
    
    If x >= limit5 Then
        RowID = 5
    End If
    
    MsgBox DLookup("ContributionThreshold", "tblContributionValues", "ID = " & RowID), vbOKOnly
    
'set Global Variable for Threshold for further calculations

  gvThreshold = DLookup("ContributionThreshold", "tblContributionValues", "ID = " & RowID)
    
    
    
        
        
    
    
End Function

Thanks for any insight,
Gary

 

[Alansidman]

Why not create a query and list the values 1 through 5 in your criteria for the ID field? You could list them individually or use the Between function. Something like this.

Select ID, JobSize, ContributionThreshhold
FROM tblContributionValues
WHERE ((tblContributionValues.ID) Between 1 and 5);

 

[GBalcom]

Hi Allan,
Thank you for your response. I'm not quite sure I explained myself well enough. All of the above is to come up with a value, that I can use for some additional calculations.

If I were to use a query, I would I allow it to come up with the 1 approximate match I require? Then how would I pull it?

Just trying to learn....

Thanks,
Gary

 

[Alansidman]

I don't know if you can come up with an approximate match without seeing more information. Perhaps you should upload your db with sample data and explain specifically what you want to do.

 

[DrallocD]

Functions should really be returning the value rather than setting a global variable but this should do pretty much the same thing:

Public Function ConThreshold(x As Currency)
    Dim rs As DAO.Recordset
    With CurrentDb
        Set rs = .OpenRecordset("SELECT ContributionThreshold AS CT from tblContributionValues where ID in" & _
                                "(SELECT nz(max(ID),5) from tblContributionValues where  JobSize <= " & x & " )", dbOpenSnapshot)
    End With
    gvThreshold = rs!CT
    rs.Close
End Function