Solved

Date field exception to the rule

Posted on 2007-11-14
38
339 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
  • 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

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…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

10 Experts available now in Live!

Get 1:1 Help Now