Handy Access VBA Functions

Risky

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

DOA

@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.