Solved

Date help

Posted on 2014-09-04
18
162 Views
Last Modified: 2014-09-05
Experts, I have the following in a Validation Rule for a [ExpectedDrawnDownDate] in a  form.

I need to test for JBIC that [ExpectedDrawnDownDate] is > todays date +30 days.  
If not JBIC then the [ExpectedDrawnDownDate] needs to be <todays date +25 days.  

following is what I have in the VAlidation Rule for [ExpectedDrawnDownDate]:
IIf([cboType]="JBIC",>Date()+30,<Date()+25)

I am sure there is something wrong with the formula.  I get the validation rule message box even if the date is >30 days for JBIC.

thanks
0
Comment
Question by:pdvsa
  • 10
  • 5
  • 3
18 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 500 total points
ID: 40304279
I would put that code in the form BeforeUpdate event procedure since you are validating against another control.

Ex.:

Select case cboType
case "JBIC"
    if ExpectedDrawnDownDate > Date() + 30 Then
    else
        Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
        Cancel = true
    end if
case else
    if ExpectedDrawnDownDate < Date() + 25 Then
    else
        Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
        Cancel = true
    end if
end select

Open in new window

0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40304296
Instead of using date() try using Now() as in:

IIf([cboType]="JBIC",>Now()+30,<Now()+25)
0
 

Author Comment

by:pdvsa
ID: 40304398
pdebaets:  I have copied the code but it doesnt seem to work as expected.  If I enter in say for instance sept 6, 2014 then I do not get a msg box if for JBIC.  

do you see an issue?
0
 

Author Comment

by:pdvsa
ID: 40304415
I did put the code in the after update of the field tho and maybe that is why it's not working...
0
 

Author Comment

by:pdvsa
ID: 40304421
liek this in the before update of the field (might have incorrectly referred to it as as the AFterUpdate):
Private Sub cboExpectedDrawnDownDate_BeforeUpdate(Cancel As Integer)

        Select Case cboType
Case "JBIC"
    If ExpectedDrawnDownDate > Date + 30 Then
    Else
        MsgBox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
        Cancel = True
    End If
Case Else
    If ExpectedDrawnDownDate < Date + 25 Then
    Else
        MsgBox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
        Cancel = True
    End If
End Select


End Sub
0
 

Author Comment

by:pdvsa
ID: 40304441
sorry for all the responses but I think I know what I did wrong.  I put the code in the Before Update and should be in the After Update.  

I dont think the forms after update is what I am after because it will not return a message until tab out of the record.

let me know if I have something ary...thanks
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40304467
You can use on current on the form level.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40304524
Now, what if the value in cboType changes?

this is why I am recommending using the form BeforeUpdate event procedure. That is the point where all values have been entered in the controls and validation between two controls can be performed.
0
 

Author Comment

by:pdvsa
ID: 40304602
if I use the form's BEforeUpdate then i dont think it is triggered until after tab out of the record, which would not be best case for me.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:macarrillo1
ID: 40304610
Then put it in both the on current for the form and on change for the field.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40304657
You could create a subroutine in your form module that contains your validation code and then call it from the cboType, cboExpectedDrawnDownDate AND form BeforeUpdate event procedures. The code should look something like this:

Sub ValidateMe
if trim("" & cboType) <> "" and trim("" & ExpectedDrawnDownDate) <> "" then
    Select case cboType
    case "JBIC"
        if ExpectedDrawnDownDate > Date() + 30 Then
        else
            Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            Cancel = true
        end if
    case else
        if ExpectedDrawnDownDate < Date() + 25 Then
        else
            Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            Cancel = true
        end if
    end select
end if
End Sub

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40304707
ok that is getting a little bit out of my knowledge but I understand a little.  I am not sure how to create a subroutine.  I dont see it as being in a the form's on current or any other events.  Maybe I just copy as is?  

I guess I call it from the BeforeUpdate of the fields?  not sure how to do that exactly.  

thank you
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40304735
You can create a subroutine in a form module in the same way you can do it in a standard module. Open any form in design mode and press Alt-F11. Navigate to the (General)  (Declarations) section by selecting these from the combo boxes on the top of the screen. Just below any "Option" statements, paste the code from above. Compile and save.

Next, go to the BeforeUpdate event procedure of each object I mentioned above and enter

ValidateMe

Open in new window


Compile and save.
0
 

Author Comment

by:pdvsa
ID: 40304844
that is how an expert does it.  I like that method.  I do have an error on compile and I know why but I dont know how to fix.  The error on compiel is Cancel = True.  I think I have to do something at the top to define or allow for this... I think "boolean".
0
 

Author Comment

by:pdvsa
ID: 40304857
I think I needed to put Cancel as Integer like this:
Sub ValidateMe(Cancel As Integer)

I call it like this though for example:
Private Sub cboType_BeforeUpdate(Cancel As Integer)

        ValidateMe


End Sub


but I get an error on compile of "Argument not Optional"  
sorry this has dragged on a bit.  I appreciate the expert help.
0
 

Author Comment

by:pdvsa
ID: 40306008
pdebaets, do you happen to know why I have an error on compile of "Argument not Optional"

here is a paste from the form:
Option Compare Database
Option Explicit

Sub ValidateMe(Cancel As Integer)
If Trim("" & cboType) <> "" And Trim("" & ExpectedDrawnDownDate) <> "" Then
    Select Case cboType
    Case "JBIC"
        If ExpectedDrawnDownDate > Date + 30 Then
        Else
            MsgBox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            Cancel = True
        End If
    Case Else
        If ExpectedDrawnDownDate < Date + 25 Then
        Else
            MsgBox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            Cancel = True
        End If
    End Select
End If
End Sub

Private Sub cboType_BeforeUpdate(Cancel As Integer)

        ValidateMe


End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:pdebaets
ID: 40306151
OK, let's make it a function that returns True if all was validated OK

Function ValidateMe as integer
Dim rtn as integer
rtn = true
if trim("" & cboType) <> "" and trim("" & ExpectedDrawnDownDate) <> "" then
    Select case cboType
    case "JBIC"
        if ExpectedDrawnDownDate > Date() + 30 Then
        else
            Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            rtn = false
        end if
    case else
        if ExpectedDrawnDownDate < Date() + 25 Then
        else
            Msgbox "Invalid Expected Draw Down Date. Press <ESC> to undo your changes."
            rtn = false
        end if
    end select
end if
ValidateMe = rtn
End Function

Open in new window


Now call it from your BeforeUpdate event procedures like this

Cancel = Not ValidateMe

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40306811
very nice.  That worked perfectly.  I appreciate the expert help with this.  Have a good weekend....
0

Featured Post

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.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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

12 Experts available now in Live!

Get 1:1 Help Now