Select Case: IsNull or Equivalent?

[theKruser]

Bottom line: Any idea how to handle the "IsNull" process?

I have a form in which the user is to input 5 dates. Three of the 5 dates must occur in sequence, meaning date one MUST come before date two, which MUST come before date three. I am building what is in essence a date sequence checker to identify data entry errors and prevent future problems. The issue is that only two of the 5 dates are required, meaning the other three will more often than not be blank. I need a way for the checker to "skip over" the dates left blank if the user does not have that information at the time of entry. With the sequence checker, I am building a message box that will identify the errors and tell the user what they did wrong at the end. Here is a snippet of what I am building. The first Select Case statement is the last of 5 (the first 4 are omitted for ease of reading, but function the same).

Any idea how to handle the "IsNull" process?

Thank you in advance for your time and help!

    Select Case Me.sdtEnd
        Case IsNull
            End Select
        Case Is < Me.sdtPtp
            msg = msg & "End date cannot be before P date." & vbNewLine
            i = i + 1
        Case Is > Me.sdtCut
            msg = msg & "End date cannot be before cutoff date." & vbNewLine
            i = i + 1
        Case Is < Me.sdtCho
            msg = msg & "End date cannot be before C date." & vbNewLine
            i = i + 1
        Case Is < Me.sdtBeg
            msg = msg & "End date cannot be before begin date." & vbNewLine
            i = i + 1
    End Select
  
    Select Case i
        Case 0
            Me.Dirty = False
            DoCmd.GoToRecord , , acNewRec
        Case 1
            msg = "You have entered conflicting dates.  " & msg
            GoTo EntryError
        Case Is > 1
            msg = "You have entered conflicting dates.  See below list:" & vbNewLine & vbNewLine & msg
            GoTo EntryError
        Case Else
           msg = "There has been an undefined error.  Please move this message box aside and send an unimpeded screen capture of the form you were working on to the application developer."
    End Select

 

[theDBguy]

Hi. Just a guess but have you tried?

Case Is Null

 

[The_Doc_Man]

I'm pretty sure that if you did a cut/paste type of screen capture from the real code, what you showed us would never work like you wanted.

   Select Case Me.sdtEnd
        Case IsNull
            End Select                 <<<<<<< this essentially negates the rest of the SELECT ladder.
        Case Is < Me.sdtPtp
...

I would also omit ALL of the "IS" keywords you were using because they sometimes lead to confusion. The word "IS" has two meanings within the context of VBA. But as it happens, ANY TIME you meant the CASE-specific usage of "IS", you can omit it and VBA will insert it if needed. Whereas the usage "IF A IS NULL" would lead to serious syntax confusion in that context.

Also, you need to consider that your CASE ladder will only catch one error at a time. If you can re-run this code, OK. But what do you do if Me.sdtEnd is simultaneously < Me.sdtPtp AND < Me.sdtBeg? I don't think your case ladder would work so wall in that situation unless it gets a shot at BOTH tests at different times.

 

[theKruser]

I'm pretty sure that if you did a cut/paste type of screen capture from the real code, what you showed us would never work like you wanted.

   Select Case Me.sdtEnd
        Case IsNull
            End Select                 <<<<<<< this essentially negates the rest of the SELECT ladder.
        Case Is < Me.sdtPtp
...

I would also omit ALL of the "IS" keywords you were using because they sometimes lead to confusion. The word "IS" has two meanings within the context of VBA. But as it happens, ANY TIME you meant the CASE-specific usage of "IS", you can omit it and VBA will insert it if needed. Whereas the usage "IF A IS NULL" would lead to serious syntax confusion in that context.

Also, you need to consider that your CASE ladder will only catch one error at a time. If you can re-run this code, OK. But what do you do if Me.sdtEnd is simultaneously < Me.sdtPtp AND < Me.sdtBeg? I don't think your case ladder would work so wall in that situation unless it gets a shot at BOTH tests at different times.

I did try to omit the "IS" keywords, but VBA puts them right back in. And when my first Case runs with "Null" vice "IsNull," the code errors on the next Case saying "Case without Select Case," so it seems "Case Null" negates the Select Case clause.

The snippet is only one of 5 snippets that evaluates each date against all other dates and adds the resulting error (if there is one) to the msg variable to diaply in a MsgBox under "EntryError:" subsection. I do see your point, however, about if one date violates two rules. I will have to rethink that for sure. Right now, if there are any errors, the Save is stopped and the user is taken back to the form to correct any issues. My thought was if one date violates two rules and the user only corrects one, when the user clicks the Save and New button, the code will run again, thus catching the second rule violation. Crude, but was all I could come up with.

Thoughts?

 

[theKruser]

Hi. Just a guess but have you tried?

Case Is Null

I did try to omit the "Is,", but when my first Case runs with "Null" vice "IsNull," the code errors on the next Case saying "Case without Select Case," so it seems "Case Null" negates the Select Case clause. When I run "Case Is Null," it errors saying it expected an operator (<, >, =, <>, etc.) following "Is."

 

[plog]

Convert the Null values to a value not possible, then add a case for that instead of the NULL:

Select Case NZ(Me.sdtEnd, -1)
  Case -1:
    End Select
  Case Is < Me.sdtPtp
            msg = msg & "End date cannot be before P date." & vbNewLine


...

 

[The_Doc_Man]

plog, am I being exceptionally dense or did you not see that there is a SELECT for which there is a legal case option followed by an END SELECT followed by other CASE options? I do not think the syntax allows that END SELECT to be conditional on the CASE in which it falls because having an "idle" CASE is legal - and in fact often used when you mean "I see this but don't care to do anything."

 

[OldeCow69]

I'd use If statements for that first Case block as that can capture all the errors, not just the first one?

Aircode:

    If Not IsNull(Me.sdtEnd) Then
        If Not IsNull(Me.sdtPtp) and Me.sdtEnd < Me.sdtPtp Then
            msg = msg & "End date cannot be before P date." & vbNewLine
            i = i + 1
        End If
        If Not IsNull(Me.sdtCut) and Me.sdtEnd > Me.sdtCut
            msg = msg & "End date cannot be before cutoff date." & vbNewLine
            i = i + 1
        End If
        If Not IsNull(Me.sdtCho) and Me.sdtEnd < Me.sdtCho
            msg = msg & "End date cannot be before C date." & vbNewLine
            i = i + 1
        End If
        If Not IsNull(Me.sdtBeg) and Me.sdtEnd < Me.sdtBeg
            msg = msg & "End date cannot be before begin date." & vbNewLine
            i = i + 1
        End If
    End If

Edit: Do'h fixed copy-paste slackness.

 

[arnelgp]

this what i have come so far:

Public Function dateValidator(ParamArray dte() As Variant) As Boolean
    Dim idx As Integer
    Dim retValue As Boolean
    Dim i As Integer
    'backward loop
    For idx = UBound(dte) To LBound(dte) Step -1
        If idx > LBound(dte) + 1 Then
            If Not IsNull(dte(idx)) Then
                retValue = (IsNull(dte(idx - 1)) = False)
                If Not retValue Then
                    i = idx
                    Exit For
                End If
                retValue = (dte(idx) > dte(idx - 1))
                If Not retValue Then
                    i = idx * -1
                    Exit For
                End If
            End If
        Else
            If idx = LBound(dte) Then
                retValue = False
                If Not retValue Then
                    i = -999
                End If
            Else
                retValue = (IsNull(dte(idx)) = False) And (IsNull(dte(idx - 1)) = False)
                If Not retValue Then
                    i = -888
                    Exit For
                End If
                retValue = (dte(idx) > dte(idx - 1))
                If Not retValue Then
                    i = -777
                End If
            End If
            Exit For
        End If
    Next
    If Not retValue Then
        Call show_error(i)
    End If
    dateValidator = retValue
End Function

Private Sub show_error(ByVal idx As Integer)
    Dim strMsg As String
    Select Case idx
        Case Is = -999
            strMsg = "date2 is required"
        Case Is = -888
            strMsg = "date1 and date2 are required"
        Case Is = -777
            strMsg = "date2 must be greater than date1"
        Case Is = -4
            strMsg = "date5 must be greater than date4"
        Case Is = -3
            strMsg = "date4 must be greater than date3"
        Case Is = -2
            strMsg = "date3 must be greater than date2"
        Case Is = 0
            strMsg = "date1 required"
    End Select
    MsgBox strMsg, vbInformation, "Date Validator"
End Sub

Private Sub testingValidator()
    'check for any date combination
    'try putting Null values to the dates, eg:
    '
    'Debug.Print dateValidator(Null, #1/2/2020#, #1/3/2020#, #1/4/2020#, #1/5/2020#)
    'Debug.Print dateValidator(#1/1/2020#, Null, #1/3/2020#, #1/4/2020#, #1/5/2020#)
    'Debug.Print dateValidator(#1/1/2020#, #1/2/2020#, Null, Null, Null)
    '
    'try also reversing the order of date by putting
    'the greater date first followed by a lesser date.
    Debug.Print dateValidator(#1/1/2020#, #1/2/2020#, #1/3/2020#, #1/4/2020#, #1/5/2020#)
End Sub