Solved

Case and Date criteria

Posted on 2014-09-16
10
125 Views
Last Modified: 2014-09-17
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

0
Comment
Question by:pdvsa
  • 6
  • 4
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
Pat, thanks.  How can I just add txtRequestedDisbDate<5  criteria to the CAse Else part?  
here:  If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
its for a case other than JBIC
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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
 

Author Closing Comment

by:pdvsa
Comment Utility
thank you!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:pdvsa
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now