I am working on a database project that needs to utilize date comparison code. I could really use some help on this one. Heres the problem below..



Problem:



I need to construct code that compares data in three fields: Assets, StartDate, and EndDate. I have a database that I want to be able to schedule the use of assets for customers from a start date to and end date. Once an asset is scheduled for a customer from the start date, it cannot be used for another customer until after the end date. If an asset is already scheduled, then an error message box will appear on my form, or if the end date is before the start date an error message box will appear.(Sort of like Library Book code that allows the check out and return date of a book where no one else can check the book out until it is returned by the customer) If there is anyone out there who knows of code that I can use to program a form button on my Access 2003 database project please help. I have tried to write it out below in this form:


On Click
1. Opens qryScheduledResourceType
2. Compares Resource Type with StartDate and EndDate.
3. If there is no conflict with the Resource Type and dates, Then a new record
                Will be saved.
4. If not, an error message will occur because either resources are already scheduled with the new dates or the end date preceeds the startdate.



I have started to try to write code for this, however I know that I am missing quite a few pieces:

Private Sub CommandSave_Click()
DoCmd.OpenQuery qryScheduleResourceType, acPreview

If ResourceType = 0 Then
Perform Save Function
ElseIf ResourceType > 0, Where StartDate (New) = between StartDate (Old) and EndDate (Old)Then
MsgBox "Scheduling Error-Resource Type Already Scheduled. Pick Another Resource Type or other Dates"
ElseIf EndDate < StartDate Then
MsgBox "End Date Error - End Date Occurs Before Start Date"
End If
End Function



I know this is crude, but it is the only way that I can come up with to explain this. I wish to tie this code, if possible to a Save record button on my form that will run this test, and either input the new record, or will produce an error message telling the customer to reschedule either another asset or dates. If you know how I can do this, I would really appreciate the help. I can be reached by reply to this email or the mail and phone number address below.

Thanks in advance.

James Jackson
813-828-4628
[email protected]