Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date help

Posted on 2014-09-04
18
Medium Priority
?
171 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
[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
  • 10
  • 5
  • 3
18 Comments
 
LVL 12

Accepted Solution

by:
pdebaets earned 2000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

730 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