Solved

Case and Date criteria

Posted on 2014-09-16
10
129 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 37

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 37

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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