Solved

Date field exception to the rule

Posted on 2007-11-14
38
344 Views
Last Modified: 2013-11-28
I have a before update code that works fine but now, I will like to create an exception to the rule.
txtAppDate is a date field used in setting date range for number of record generated monthly.

If a record is cancelled, then I want to update tblMain.Appdate to an invalid date like 99\99\99 such that this date can never be utilized for counting records monthly.

At the form level, my before update event code might present a dilemma when user navigates to the record containing the invalid date. Looking at the code, is there any way to set an exception to an invalid date 99\99\99 for example such that the before update code ignores the entry when user navigates to the cancelled record?
However, I still want the before update code to function as it was originally designed for users wanting to validate new date entry. I am not sure this is possible - I just want to explore the possibility.
' **********
Before update event of the control

If Me.txtAppdate > Date() Then
    Msgbox "The date you entered is not valid", , "Bad Date"
    Cancel = True
End If
0
Comment
Question by:billcute
[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
  • 16
  • 6
  • 6
  • +2
38 Comments
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 100 total points
ID: 20286437
If Me.txtAppdate > Date() and Me.txtAppdate <> "99/99/99" Then
    Msgbox "The date you entered is not valid", , "Bad Date"
    Cancel = True
End If
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 20286564

If Not IsDate(Me.txtAppdate)  or Me.txtAppdate > Date()  Then
    Msgbox "The date you entered is not valid", , "Bad Date"
    Cancel = True
End If

mx
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20286618
obviously 99/99/99 is not a valid date, not even a legal date (there aren't 99 days). but if it's set to 2999/12/31, then isn't IsDate(me.txtAppDate) still true?
0
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!

 
LVL 75
ID: 20286644
Not sure I understand your question?  The purpose of Not IsDate() in this case is to prevent someone typing in 'abcxyz' for a 'date', ie a text value or whatnot.

Me.txtAppdate <> "99/99/99"   is only covering one case.  What if it's  "98/98/98" ?

Not IsDate() covers that.

mx
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20286801
What does this mean?

[
If a record is cancelled, then I want to update tblMain.Appdate to an invalid date like 99\99\99 such that this date can never be utilized for counting records monthly.
]
[when user navigates to the record containing the invalid date]

Forgive my english, but I think it means that the some date value is persisted into the database. and it's coming back from db
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20287253
If I understand correctly, you are asking how to store "99/99/99" in a date field to represent a special situation where the record has been cancelled.

You cannot do that.  You can use a "special date", like 12/31/9999 to mean the same thing--it's just as easy to program that special case as 99/99/9999.  Alternatively, you can add a status field to the record to indicate it is cancelled.
0
 
LVL 75
ID: 20287269
I suggest you do *not* store dates that do not exist in the actual data. Otherwise, you will always have to 'jury rig' around that scenario (in queries, code, etc.) ... just not a good idea.  There is *always* a better way.

mx
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20287394
>I suggest you do *not* store dates that do not exist in the actual data.

I tend to agree. But, as a practical matter:
suppose we have a record that contains an "effective date" field.  The record gets cancelled, so there is no effective date that applies. How do you suggest populating the record? What is the better way?  




 
0
 
LVL 4

Author Comment

by:billcute
ID: 20288140
imitchie / dqmq / mx /

All arguments seems logical and this is why I am providing further explanation here to clarify things:
(a). User selects a "No" item from cboA (bounded to tblMain)
       the AfterUpdate event sql ...updates tblMain.Appdate to something like 12/31/2999 (an invalid date)
       such that the AppDate field will not count the invalid date as part of the monthly report.

(b). In the main form "frmMain" the txtAppdate can present a problem because of the Before Update event code posted above ..if and when user navigates to the record through a record search.
therefore, a code to ignore 12/31/2999 at the before update event handler.

Note that that in the first place, user will not be allowed to enter an invalid date in the first place but an exception is being made to the rule where the date 12/31/99 or 12/31/2999 is ignored..noting that 12/31/99 or 12/31/2999 will be constant to all records where tblMain.Cancelled field is set to "No"
' ******************
In another related post on EE - currently listed by me, you will find a code that allows supervisor "override" in the event of the following:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_22962019.html

This means that
(1). I wanted to keep the existing "cancelled" record in the table and still be able to view them except in the locked mode.
(2). A supervisor has the "write" permission to re-activate the cancelled job, change the Appdate to a valid date again, if for example applicant shows up again with an updated info.

I hope this clearify things better now.

Regards
Bill
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20288181
which i thought my first comment addressed? it skips the check when the date matches your const invalid date
0
 
LVL 75
ID: 20290102
"What is the better way?"

One way is to have a 'Cancelled' Yes/No field ... checked if cancelled ... Effective date remains in tact.

mx
0
 
LVL 4

Author Comment

by:billcute
ID: 20294080
imitchie,
I changed txtAppdate input mask to 99/99/0000;0 then tested your suggestion by entering 99/99/99 or 12/31/2999at different times. I recieved a Microsoft dialog msgbox:
"The value you entered is not appropropriate for this input mask '99/99/0000;0' specified for this field.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20294109
mx,
 I tried your code as well with input mask 99/99/00;0   and 99/99/0000;0.
I received the same error msgbox I received earlier will testing for imitchie's code.

Regards
Bill
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20294138
I would just use a format rather than an input mask.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20294218
try mask :   99/99/0000;0;_
and try 31/12/2999, 12/31/2999
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20294302
>One way is to have a 'Cancelled' Yes/No field ... checked if cancelled ... Effective date remains in tact.

Well, yes, and I believe I said as much in my original solution. However, what still escapes me is how that avoids "jury rigging" in code.  I mean, I don't much see the qualitative difference between:

Select * from Table where cancelled_fg = "N"
and
Select * from Table where effective_date <> 12/31/9999

Furthermore, dates are often used as criteria in their own right.  For example, show all the rows that are effective during January.   So, when using the special date technique, that becomes:

Select * from Table where effective_date between 1/1/2007 and 1/31/2007

However, with the status flag technique, one needs to add the condition:
   and cancelled_fg = "N"

Seems to me like that's more hoops to jump thru than just using the special date.  

--dQmQ

 




 

0
 
LVL 4

Author Comment

by:billcute
ID: 20294654
dqmq;
Ok,
 let's try your technique...but this question is not an isolated incident...it means that you may need to combine the two questions to evolve a solution. In this case,
I will appreciate it if you could post a sample here for everyone to see and analyze for the two posts.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20294668
imitchie:
I tried the input mask: 99/99/0000;0;_
and and tried 31/12/2999, 12/31/2999....

...I received the Microsoft Dialog msgbox.

Regards
Bill
0
 
LVL 75
ID: 20296587
" I mean, I don't much see the qualitative difference between:"
Big difference:
Two separate fields.  The date field contains only 'real' dates in the data (ie, a real cancelled date) ... no phony dates'. The Yes/No field quantifies the Cancelled condition.

", one needs to add the condition:
   and cancelled_fg = "N""

Aahhh ... well, so what? Criteria is 'cheap'.  From a relational standpoint .. it's a bit more 'correct'.  Hey ... I'm not saying you 'have to' do that ... it's just what I would (and have) done.

mx
0
 
LVL 75
ID: 20296590
"I would just use a format rather than an input mask."

Two completely different things for two completely different uses.

mx
0
 
LVL 4

Author Comment

by:billcute
ID: 20297070
mx,
please post your post your suggestied idea in full.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20310210
To All Experts:
Please download a sample of my db from the link below:
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=5692 

I have posted both the MX / imitchie:suggested code at the Before Update Event. The test will show that both suggested code did not ignore the invalid date.

TESTING:
(1). Open the db and then select "Yes" from the cboCancel combo.
(2). Take note that the txtAppdate was updated to 12/31/2999
(3). Click the Add new Record to save the record then
(4). You'll notice that record cannot be saved due to the Before Update code.

Any further help will be appreciated.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20310250
dqmq:
With posted sample, I will appreciate it if you could amend the sample to suit the suggestions you proposed above and post the amended sample here.

Regards
Bill
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 100 total points
ID: 20310844




Option Compare Database  ' Use database order for string comparisons.
Option Explicit  ' Requires variables to be declared before they are used.
Const kCancelDate = #12/31/2999#   'dQmQ special date that means cancelled
 
Private Sub btnAddNew_Click()
 
On Error GoTo Err_btnAddNew_Click
 
    If IsNull(Me.txtConnType) Then
   MsgBox "Please enter ConnType"
   Exit Sub
End If
    
'OK
DoCmd.GoToRecord , , acNewRec
    
Exit_btnAddNew_Click:
    Exit Sub
Err_btnAddNew_Click:
    MsgBox Err.Description
    Resume Exit_btnAddNew_Click
End Sub
 
Private Sub btnPrev_Click()
On Error GoTo Err_btnPrev_Click
 
 
    DoCmd.GoToRecord , , acPrevious
 
Exit_btnPrev_Click:
    Exit Sub
 
Err_btnPrev_Click:
    MsgBox Err.Description
    Resume Exit_btnPrev_Click
    
End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click
 
 
    DoCmd.GoToRecord , , acNext
 
Exit_btnNext_Click:
    Exit Sub
 
Err_btnNext_Click:
    MsgBox Err.Description
    Resume Exit_btnNext_Click
    
End Sub
 
 
Private Sub btnClose_Click()
      ' This code created by Command Button Wizard.
    On Error GoTo Err_btnClose_Click
 
          ' Close form.
        DoCmd.Close
          
Exit_btnClose_Click:
        Exit Sub
          
Err_btnClose_Click:
        MsgBox Err.Description
        Resume Exit_btnClose_Click
 
End Sub
 
Private Sub cboCancel_AfterUpdate()
If cboCancel = "Yes" Then
       MsgBox "You selected to cancel this record"
       Me.txtAppDate = kCancelDate   'dQmQ '*** this assumes that the current record on the form has [SID] = Me.txtSID
    End If
 
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' ***imitchie Amended Code******
    If Not IsDate(Me.txtAppDate) Or (Me.txtAppDate > Date And Me.txtAppDate <> kCancelDate) Then   'dQmQ
        MsgBox "The date you entered is not valid", , "Bad Date"
        Cancel = True
    End If
' **** End of imitchie Amended Code*******
' **************************************************
    ' ***MX Amended Code******
'    If Not IsDate(Me.txtAppDate) Or Me.txtAppDate > Date Then
'        MsgBox "The date you entered is not valid", , "Bad Date"
'        Cancel = True
'    End If
' **** End of MX Amended Code*******
 
End Sub

Open in new window

0
 
LVL 4

Author Comment

by:billcute
ID: 20311307
dqmq:
Thanks for the suggestion of constant declaration. However, there is a problem.

Try entering date greater than today's date (11/19/07)

....such as 11/21/07; 11/31/07; 12/31/07.

You'll notice that the Before Update Event did not stop the entry.

By design any date entered that is greater than today's date should not be allowed..

Is there a way to fix this?

Regards
Bill
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20314673
Bill,
I cannot reproduce that behavior.  When I enter a future date, I get the error message.  Please post your before_update event.  

Also, note that it's possible to cancel a record, forcing in the special date, then to subsequently modify the record to a legitmate date.  When I respond to above, post, I will give you a fix for that as well.


0
 
LVL 4

Author Comment

by:billcute
ID: 20316876
dqmq:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' ***imitchie Amended Code******
    If Not IsDate(Me.txtAppDate) Or (Me.txtAppDate > Date And Me.txtAppDate <> kCancelDate) Then
        MsgBox "The date you entered is not valid", , "Bad Date"
        Cancel = True
    End If
' **** End of imitchie Amended Code*******
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 200 total points
ID: 20319020
I may not have a firm grasp of the issue here but from what I understand you don't want cancelled records included in your monthly reports.

If this is the only issue then I don't see any reason to change your before update event at all. ie leave as follows.

If Me.txtAppdate > Date() Then
    Msgbox "The date you entered is not valid", , "Bad Date"
    Cancel = True
End If

Importantly however, (and I agree with above posts that discourage the use of invalid data such as 99/99/99) I would use the cboCancel after update event as follows.

Private Sub cboCancel_AfterUpdate()
If cboCancel = "Yes" Then
        MsgBox "You selected to cancel this record"
       Me.txtAppDate = Null
       Me.Refresh
End If
End Sub

This will set the AppDate to a Null which will be ignored by any date selection criteria, Will not cause issues with your before update event, and is perfectly legal in terms of Access data validation and allowed by your table design. It also avoids the issue of ensuring that the correct "Invalid" date is used.

As I said I may not have a firm grasp of the issue, Im just having a stab in the dark.  This solution just seems too simple.

Leigh
0
 
LVL 4

Author Comment

by:billcute
ID: 20324378
Leigh:
You really got me thinking. Your summation was very good and I'll give it a second thought.

However, their is a problem.
    txtAppDate is a mandatory field- meaning it is a required field, so setting the field to "Null"
       will be a real problem.

Any other suggestion around this?

Regards
Bill
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20324573
In the database sample that was available for download it wasn't set in the table as a mandatory field ???
0
 
LVL 4

Author Comment

by:billcute
ID: 20324754
Leigh:
I do have code that checks and display all mandatory fields when the Add button or Exit button is clicked. It was not part of the sample.

Regards
Bill
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20324799
That shouldn't be hard to overcome, Simply  add an ignore clause to the Add and Exit button procedures for cancelled records.
0
 
LVL 4

Author Comment

by:billcute
ID: 20324870
How? The problem is that the date field is very important to monthly record and there is a tendency user will forget to add a date when creating a new record.

Regards
Bill
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20324933
Yes, but that shouldn't be a problem because a new record won't be cancelled so the data validation should still function as normal.

ie, the date validation will still function for new and existing records that aren't cancelled it will just ignore the cancelled ones.
0
 
LVL 4

Author Comment

by:billcute
ID: 20324987
okay,
in this case, will you assist with the code?
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20325017
Not a problem,

all that should be required is something like this in your add new and exit button procedures.


if me.cboCancel = "Yes" then exit sub

< then your datefield validation code >

that way if the record is cancelled date validation will not occur which means that the null date will be ok, but if cboCancel = "No" then the data validation code will operate as normal.

If you post the code for these two button I'll give it a crack.
0
 
LVL 4

Author Comment

by:billcute
ID: 20325139
Leigh:
Thanks but that will be drifting away from the original question here as such I will close this current post.

...... and in order to credit experts for their efforts, I have decided to create a separate question at the link below to explore the new idea.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_22974510.html

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20325147
To All Experts:
I have decided to split the points here to credit the experts efforts.

Regards
Bill
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
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 …

710 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