By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,183 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,183 IT Pros & Developers. It's quick & easy.

Date validation within MS Access

P: 8
I must start this posing by making the point that I am NOT a VB programmer and I'm something of a Newbie to MS Access. I can program in a number of languages (Java, Javascript, PERL,PHP and TCL) but have never actually learnt VB so my request is that you bear this in mind if you plan on replying to this post.

My request for help is as follows:

I have an MS Access database in which one of the tables within it contains two date fields called "StartDate" and "EndDate" respectively. The table in question is designed to hold entity specific data on conferences hosted by a conference hosting company. Each row in the database contains a new record (entity instantiation) of a specific conference hosted by the company.

The field names are self-explanatory in that the StartDate refers to the date at which a conference started or starts and the EndDate fields refers to the either a future date when the conference will end or when it ended some time in the past. The problem I am having is that I need to apply validatory constraints to both of these fields that are not easily achieved with the application and so therefore I suspect that I need to resort to some VB code.

To be clear, there are cerrtain rules that I need to apply (constraints) to these date fields to ensure that any values entered via a conrresponding entry form or indeed via a datasheet view for a completely new or existing record do not violate these rules. The rules are:

START DATE:

- The value entered cannot be today's date - i.e, it must be greater than today's date and therefore some date in the future.

- The value entered can't be after the value entered in the end date as this mean that in effect a conference could never start.

END DATE:

- The value entered cannot be today's date - i.e, it must be greater than today's date and therefore some date in the future.

- The value entered cannot be before the value entered in the start date field as this would imply that the conference had already finished and all data about conferences will always be entered into the database before the actual start of a conference.

I do not know whether I can utilise code that will operate at the datasheet view level or whether it can operate only via a form but either way, I hope what I'm trying to achieve is clear. The question then is how to do this. I suspect that I am going to need to use VB code which is why I mentioned not being a VB programmer at the outset.

I did think of trying to achieve this by means of two additional database tables: - one called, something like: TBLConferenceStartDates and another called: TBLConferenceEndDates. Given that each record in the main conference details table has a unique ID assigned to it as a primary key (of datatype Integer), one could reference the conference start date and conference end date tables via a foreign key whilst maintaining referential integrity. The task then would be to use a the result of lookup query as the basis for enforcing the validation rules. One could look up the specific start date for a given conference ID, and vice-versa for an end date but I do not know whether it's possible to have a query run and then use the result of a query as a validation rule within Access. If it is possible, I'd love to know how this is achieved.

Any ideas with an explanation as to how any code works, would be very gratefully received.
Nov 27 '07 #1
Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
Having separate tables for start dates and end dates is abit too much normalization! Assuming you're doing your data entry thru a form (and you should never do data entry thru the table) this should do the job!

Expand|Select|Wrap|Line Numbers
  1. Private Sub EndDate_BeforeUpdate(Cancel As Integer)
  2. If IsNull(Me.StartDate) Then
  3.   MsgBox "You Must Enter a Start Date Before Entering an End Date !"
  4.     Cancel = True
  5.     Me.EndDate.Undo
  6.    End If
  7.  
  8. If Me.EndDate < Me.StartDate Then
  9.   MsgBox "End Date Must Be The Same or Later Than The Start Date !"
  10.   Cancel = True
  11.   Me.EndDate.SelStart = 0
  12. End If
  13. End Sub
  14.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub StartDate_BeforeUpdate(Cancel As Integer)
  2. If Not StartDate > Date Then
  3.   MsgBox "Start Date Must Be a Date In The Future !"
  4.   Cancel = True
  5.   Me.StartDate.SelStart = 0
  6. End If
  7.  
  8. If Not IsNull(Me.EndDate) Then Me.EndDate = ""
  9.  
  10. End Sub
  11.  
  12.  
It takes care of your validation and, in case the start date is entered incorrectly and then editted, it requires that the end date be re-entered, so that it is again validated.

Welcome to TheScripts!

Linq ;0)>
Nov 27 '07 #2

P: 8
Having separate tables for start dates and end dates is abit too much normalization! Assuming you're doing your data entry thru a form (and you should never do data entry thru the table) this should do the job!

Expand|Select|Wrap|Line Numbers
  1. Private Sub EndDate_BeforeUpdate(Cancel As Integer)
  2. If IsNull(Me.StartDate) Then
  3.   MsgBox "You Must Enter a Start Date Before Entering an End Date !"
  4.     Cancel = True
  5.     Me.EndDate.Undo
  6.    End If
  7.  
  8. If Me.EndDate < Me.StartDate Then
  9.   MsgBox "End Date Must Be The Same or Later Than The Start Date !"
  10.   Cancel = True
  11.   Me.EndDate.SelStart = 0
  12. End If
  13. End Sub
  14.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub StartDate_BeforeUpdate(Cancel As Integer)
  2. If Not StartDate > Date Then
  3.   MsgBox "Start Date Must Be a Date In The Future !"
  4.   Cancel = True
  5.   Me.StartDate.SelStart = 0
  6. End If
  7.  
  8. If Not IsNull(Me.EndDate) Then Me.EndDate = ""
  9.  
  10. End Sub
  11.  
  12.  
It takes care of your validation and, in case the start date is entered incorrectly and then editted, it requires that the end date be re-entered, so that it is again validated.

Welcome to TheScripts!

Linq ;0)>

Thank you!

This is exactly what I wanted. Not being a VB programmer, there is no way could have done this myself and so I'm extremely grateful.
Nov 28 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help! It was actually a nice little problem to work on! If you have any trouble post back.

Linq ;0)>
Nov 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.