[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Case and Date criteria

Posted on 2014-09-16
10
Medium Priority
?
133 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 41

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 41

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 41

Accepted Solution

by:
PatHartman earned 2000 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 41

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 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