Handy Access VBA Functions



  • A lot of my job is tidying up ancient access databases.  I don't mind it too much where the user clearly knew no coding at all but sometimes you have to wonder what they were smoking. Hence this collection all from a single module in a '97 system.  As you all know it's best to avoid user defined functions in Access queries, but here not only are they completely unnecessary but the naming is wonderfully obtuse:

     'X=X possible the best line of the lot
    Function rpdbl(X)
     If IsNull(X) = True Or LenB(Trim(X)) < 1 Then
            X = 0
        Else
            X = X
        End If
        rpdbl = X
    End Function

    'How do you find out if something is zero?
    Public Function CountIf(numIn) As Integer
    On Error Resume Next
    If IsNumeric(numIn) Then
        If numIn > 0 Or numIn < 0 Then
            CountIf = 1
        Else
            CountIf = 0
        End If
    End If
    End Function

    'Exactly what does this sum?
    Public Function SumIf(numIn) As Double
    On Error Resume Next
    If IsNumeric(numIn) Then
        If numIn > 0 Or numIn < 0 Then
            SumIf = Abs(numIn)
        Else
            SumIf = 0
        End If
    End If
    End Function


    'He knows ADO but doesn't know Dlookup
    Function FindDate(tblName, dte)
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim d As String
       
        Set cnn = New ADODB.Connection
        Set rst = New ADODB.Recordset
       
        d = CurrentDb.name
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & d & "; Jet OLEDB:Engine Type=4;"
        rst.Open "SELECT COB FROM " & tblName & " GROUP BY COB;", cnn, adOpenKeyset, adLockOptimistic
         FindDate = False
        With rst
            .Find "COB = #" & dte & "#"
            Do While Not .EOF
                FindDate = True
                 rst.Close
                Set cnn = Nothing
                Exit Function
            Loop
        End With
        rst.Close
        Set cnn = Nothing
    End Function

    'NZ() anyone?
    Function moves(a, b)
        If IsNull(b) = True Then
            moves = a
            Else
                moves = a - b
        End If
    End Function

    'Just painful
    Function wds(d As Date) As Date
        Dim a As Integer
        a = WeekDay(d, 2)
        Select Case a
            Case 1
            wds = d
            Case 2
            wds = d
            Case 3
            wds = d
            Case 4
            wds = d
            Case 5
            wds = d
            Case 6
            wds = d - 1
            Case 7
            wds = d - 2
        End Select
    End Function

     



  • @Risky said:

    A lot of my job is tidying up ancient access databases.
     

    I will forego commenting on specific code and simply generalise by quoting Scary Movie: Run bitch! RUUUUN! 

     



  • Looks like it was made by somebody who was only experienced with Excel and wanted to recreate that environment, but didn't have the faintest clue how to go about it. 

    <hints id="hah_hints"></hints>


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.