Help Selecting Variables

[bessej43]

I have a zip file attached to try to make this a little easier to explain. What I have is a couple of variables; I need assistance in determining how it should be written. I have a rate chart table (2002DailyRate) and depending on the Grade (id) and the number of year (salary##), populate the data in a different table. If you look at the design view it will give you the definitions for the salary. Below is an example:

Grade: E7 (id 22)
Year in Service: 20 (salary14)
Rate: $109.76 (put results in Catch62 table)

As you can see there are 29 ids and 16 salaries. Can anyone tell or show me the best way to get 464 different variables without writing a book? I am not asking you to write it but to assist with which way it should be written. If you could write one line I will continue with the rest. Any assistance would be greatly appreciated. Thank you.

 

[Travis]

Public Function GetRate01(stGrade As String, inYears As Integer) As Single
    'With an ADO Recordset
    Dim rstValue As ADODB.Recordset
    Dim cnnValue As ADODB.Connection
    Dim inField As Integer
        
        Set rstValue = New ADODB.Recordset
        Set cnnValue = New ADODB.Connection
        Set cnnValue = CurrentProject.Connection
        
        Select Case inYears
            Case Is < 2:  stField = 1
            Case Is < 3:  stField = 2
            Case Is < 4:  stField = 3
            Case Is < 6:  stField = 4
            Case Is < 8:  stField = 5
            Case Is < 10:  stField = 6
            Case Is < 12:  stField = 7
            Case Is < 14:  stField = 8
            Case Is < 16:  stField = 9
            Case Is < 18:  stField = 10
            Case Is < 20:  stField = 11
            Case Is < 22:  stField = 12
            Case Is < 24:  stField = 13
            Case Is < 26:  stField = 14
            Case Is < 28:  stField = 15
            Case Is > 27:  stField = 16
            Case Else
                'Does not match any case
        End Select
        
        rstValue.Open "Select [Salary" & stField & "] From [2002 Daily Rate] Where [Grade]='" & stGrade & "'", cnnValue, adOpenStatic, adLockReadOnly
        
        If rstValue.RecordCount > 0 Then
            GetRate01 = rstValue.Fields(0)
        End If
        
        rstValue.Close
        cnnValue.Close
        Set rstValue = Nothing
        Set cnnValue = Nothing

End Function

Public Function GetRate02(stGrade As String, inYears As Integer) As Single
    'With DLookup
    Dim inField As Integer
        
        Select Case inYears
            Case Is < 2:  stField = 1
            Case Is < 3:  stField = 2
            Case Is < 4:  stField = 3
            Case Is < 6:  stField = 4
            Case Is < 8:  stField = 5
            Case Is < 10:  stField = 6
            Case Is < 12:  stField = 7
            Case Is < 14:  stField = 8
            Case Is < 16:  stField = 9
            Case Is < 18:  stField = 10
            Case Is < 20:  stField = 11
            Case Is < 22:  stField = 12
            Case Is < 24:  stField = 13
            Case Is < 26:  stField = 14
            Case Is < 28:  stField = 15
            Case Is > 27:  stField = 16
            Case Else
                'Does not match any case
        End Select
        
        GetRate02 = DLookup("[Salary" & stField & "]", "[2002 Daily Rate]", "[Grade]='" & stGrade & "'")

End Function

 

[bessej43]

VBA Code

Travis,

Thanks for the code, you are a big help, just one more thing. Where do I save it to? On open form? This will have to run each day to keep track of pay because of the variables. Again thanks for all the help. The program is coming right along.

 

[Travis]

put it in a Module. With the Public functions in a Module you can call them from anywhere (Forms/Reports/Queries).