Case and Date criteria

Experts, I am not sure how to modify the Case Else part.  
I need to add additional criteria if the txtRequestedDisbDate <5
see underlined part below.  

thank you

Function ValidateMe() As Integer
Dim rtn As Integer
rtn = True
If Trim("" & cboType) <> "" And Trim("" & txtExpectedDisbRQDate) <> "" And Trim("" & txtRequestedDisbDate) <> "" Then
    Select Case cboType
    Case "JBIC"
        If txtRequestedDisbDate > txtExpectedDisbRQDate + 30 Then
        Else
            MsgBox "Invalid Expected Draw Down Date for JBIC. Must give at least a 30 day notice.  Press <ESC> to undo your changes."
            rtn = False
        End If
    Case Else
     [u]   If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then[/u]
        Else
            MsgBox "Invalid Expected Draw Down Date for Nexi or Uncovered. Nexi requires to be notified no greater than 25 days notice (needs to be <25 days).  Press <ESC> to undo your changes."
            rtn = False
        End If
    
    End Select
End If
ValidateMe = rtn
End Function

Open in new window

pdvsaProject financeAsked:
Who is Participating?
 
PatHartmanCommented:
Your description of what you want is still contradictory so I created both.  The first example looks for everything OUTSIDE of a window and the second looks for everything INSIDE.

Function ValidateMe() As Integer
Dim rtn As Integer
rtn = True
If Trim("" & cboType) <> "" And Trim("" & txtExpectedDisbRQDate) <> "" And Trim("" & txtRequestedDisbDate) <> "" Then
    Select Case cboType
    Case "JBIC"
        If txtRequestedDisbDate  < txtExpectedDisbRQDate + 5 or  txtRequestedDisbDate > txtExpectedDisbRQDate + 30 Then
        Else
            MsgBox "Invalid Expected Draw Down Date for JBIC. Must give at least a 30 day notice.  Press <ESC> to undo your changes."
            rtn = False
        End If
    Case Else
     [u]   If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then[/u]
        Else
            MsgBox "Invalid Expected Draw Down Date for Nexi or Uncovered. Nexi requires to be notified no greater than 25 days notice (needs to be <25 days).  Press <ESC> to undo your changes."
            rtn = False
        End If
    
    End Select
End If
ValidateMe = rtn
End Function

Open in new window


Function ValidateMe() As Integer
Dim rtn As Integer
rtn = True
If Trim("" & cboType) <> "" And Trim("" & txtExpectedDisbRQDate) <> "" And Trim("" & txtRequestedDisbDate) <> "" Then
    Select Case cboType
    Case "JBIC"
        If txtRequestedDisbDate  Between txtExpectedDisbRQDate + 5 And txtExpectedDisbRQDate + 30 Then
        Else
            MsgBox "Invalid Expected Draw Down Date for JBIC. Must give at least a 30 day notice.  Press <ESC> to undo your changes."
            rtn = False
        End If
    Case Else
     [u]   If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then[/u]
        Else
            MsgBox "Invalid Expected Draw Down Date for Nexi or Uncovered. Nexi requires to be notified no greater than 25 days notice (needs to be <25 days).  Press <ESC> to undo your changes."
            rtn = False
        End If
    
    End Select
End If
ValidateMe = rtn
End Function

Open in new window

0
 
PatHartmanCommented:
Your problem statement is incomplete.  I can only guess how to code it.

        If txtRequestedDisbDate > txtExpectedDisbRQDate + 30  AND txtRequestedDisbDate < someexpression involving  a 5 Then
        Else
            MsgBox "Invalid Expected Draw Down Date for JBIC. Must give at least a 30 day notice.  Press <ESC> to undo your changes."
            rtn = False
        End If

Open in new window

0
 
pdvsaProject financeAuthor Commented:
Pat, thanks.  How can I just add txtRequestedDisbDate<5  criteria to the CAse Else part?  
here:  If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PatHartmanCommented:
txtRequestedDisbDate < 5 doesn't make any sense if txtRequestedDisbDate is a date.  If it isn't a date, you need to adopt better naming standards.  What is the complete expression?
I added the expression to the Else path of the Case statement.  You really are not being specific regarding what you want.  Why not try to write it out yourself and we'll fix the syntax.
0
 
pdvsaProject financeAuthor Commented:
ok let me see if I can write this:

If txtRequestedDisbDate  < txtExpectedDisbRQDate + 5 or if txtRequestedDisbDate > txtExpectedDisbRQDate + 25
basically need txtRequestedDisbDate to be between txtRequestedDisbDate +5 or <25
I
0
 
pdvsaProject financeAuthor Commented:
its for a case other than JBIC
0
 
pdvsaProject financeAuthor Commented:
I think I got it.  I believe this statement below makes the logic backwards (for lack of a better way of explaining).  

Private Sub txtRequestedDisbDate_BeforeUpdate(Cancel As Integer)

    Cancel = Not ValidateMe
   
End Sub
0
 
pdvsaProject financeAuthor Commented:
thank you!
0
 
PatHartmanCommented:
Private Sub txtRequestedDisbDate_BeforeUpdate(Cancel As Integer)

     Cancel = Not ValidateMe
     
 End Sub

Open in new window


Although that code is correct, it requires too much thought on the part of a programmer to understand what is actually happening so I would spell it out like a curmudgeon.  One of my earliest programming teachers told us that people more easily understand positive logic than negative logic so write your code "positively" unless it is running inside a loop and needs to be optimized.  In that case, you always test for the most frequent "true" result.

Private Sub txtRequestedDisbDate_BeforeUpdate(Cancel As Integer)
    If ValidateMe = False Then
        Cancel = True
    End If     
 End Sub

Open in new window

0
 
pdvsaProject financeAuthor Commented:
Yes, I agree with you on that pt.  I worked with another expert with this Case logic and initially the logic was positive but I had an error and I don't remember exactly what the error was but he added the Not part in the Not ValidateMe and that was the workaround for the error I received.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.