Handle incorrect values passed to an Access VBA function

7
votes

I have a VBA function that is basically along these lines:

Public Function JoinDateTime(DateTime As String, Time As String) As Date
    Dim dtDate As Date
    dtDate = CDate(Format(DateTime, "dd/mm/yyyy"))
    dtDate = dtDate & " " & Format(Time, "hh:mm")
    JoinDateTime = dtDate
End Function

It glues a date and a time together into a datetime value. (The real function has some more logic behind it.)

The problem is, I'd like to add handling for annoying values being passed to it. This is mostly for empty/null values - if DateTime is empty, return empty. If it's a text string returning #Error so it doesn't just fail silently seems like a good idea.

Problem is, I'm not sure how to do so. I'd thought about doing an early return, maybe something like shoving this at the start of the function:

If DateTime = Null or DateTime = "" Then 
    JoinDateTime = Null
End If

but it doesn't seem to consider that as a return and still executes the rest of the function.

Is there a way to do this? A better way, ideally?

4
ms-accessvba
Why not just type the parameters as dates and then test each one for whether it has the appropriate parts? That is, the date argument must be interpreble as an integer value, and the time part should be less than 1. I just think you're asking for trouble trying to do this kind of thing. I can't even figure out why you'd even need it. Aren't you storing your data in date fields? If so, even if you have the date and time in separate fields you should then have no trouble concatenating the results into a valid time -- no complicated function needed at all! – David-W-Fenton
@David Re: Why: We're translating data that clients have sent us into one consistent format to run benchmarking reports. Our database uses a date-time format, but the clients tend to send the data as a "date" and a "time". The function glues them together, but also handles things like when they just send us the hour (i.e. for 1PM they send through an integer "13") - this is part of the 'additional logic' I was mentioning. Since the field is optional, I don't want it to fall over when they don't provide the data. Re: Validation: I'll look into it. – Margaret

4 Answers

4
votes

To prematurely return from a function in VBA, you need to use the Exit Function statement, similar to Exit Sub, Exit For, etc. So, this

If DateTime = Null or DateTime = "" Then 
    JoinDateTime = Null
    Exit Function 'Au revoir
End If

will prevent the rest of the code below from executing.

2
votes

First off, you need to change your function declaration to use Variant instead of String. This is because in VBA, the String data type cannot hold the value Null. If you want your function to return Null for invalid dates, then you will need to change the return type to Variant as well. I would also take Mitch Wheat's advice and rename your arguments to something that doesn't conflict with built-in functions (such as dateSection and timeSection).

Second, the comparison DateTime = Null will never evaluate to true. Any comparison to a Null value will result in a Null value. Instead, you should use the IsNull() function (see Error 5).

Third, you might consider using the IsDate() function. This function will ensure that the argument is a valid date, which includes checking for nulls. It depends on whether you want poorly formatted dates to fail silently, too.

Putting it all together:

Public Function JoinDateTime(dateSection As Variant, timeSection As Variant) As Variant
    Dim dtDate As Date
    If IsNull(dateSection) Then
        JoinDateTime = Null
    Else
        dtDate = CDate(Format(dateSection, "dd/mm/yyyy"))
        dtDate = dtDate & " " & Format(timeSection, "hh:mm")
        JoinDateTime = dtDate
    End If
End Function
2
votes

The cleanest and most error-proof way to do this is by raising an error. 

Public Function JoinDateTime(DateTime As String, Time As String) As Date
    If DateTime = "" Then 
        Err.Raise _
            Number:=12345, _
            Source:="JoinDateTime", _
            Description:="Invalid input. DateTime cannot be empty string."
    End If
    'NB: could also check for IsNull(DateTime) if DateTime were type Variant.
    ' etc.

This will display an error with the following message: "Run-time error '12345': Invalid input. DateTime cannot be empty string." and execution will stop.

If you don't want execution to stop, you can handle the error in the procedure that calls JoinDateTime. For example:

Sub tester()

    On Error GoTo ErrorHandler ' Telling VBA where to go when an error is raised

    Dim d As String
    Dim j As Date        
    d = InputBox("Date please:")
    j = JoinDateTime(d) ' Raises an error upon invalid input
    MsgBox Prompt:=j

ErrorHandler:
    Select Case Err.Number
    Case 12345 ' "Invalid input" error was raised, therefore do the following...
        ' Whatever you want to happen when this error occurs, e.g.
        ' MsgBox Prompt:=Err.Description
        ' or 
        ' j = 0
        ' Resume Next
    End Select
    Resume ExitProcedure

ExitProcedure:
    'Cleanup code goes here
End Sub
1
votes
If Nz(DateTime, "") = "" Then 
    JoinDateTime = Null
End If

Two points: It might be better to return a 'special' date (like start of the epoch) rather than returning null; also 'DateTime' is not a great name for a variable.