Solved

Case and Date criteria

Posted on 2014-09-16
10
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40326184
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
ID: 40326293
Pat, thanks.  How can I just add txtRequestedDisbDate<5  criteria to the CAse Else part?  
here:  If txtRequestedDisbDate < txtExpectedDisbRQDate + 25 Then
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40326527
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pdvsa
ID: 40326608
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
ID: 40326759
its for a case other than JBIC
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40328056
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
ID: 40328499
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
ID: 40328501
thank you!
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40328549
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
ID: 40328686
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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