Unanswered: Need Help with a date comparison database
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..
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:
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"
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.
What I have is a form that has four combo boxes: Customer, Resourcetype, StartDate, and EndDate. On the form I select a Customer, a Resource Type, a StartDate and an EndDate. I then have a SaveRecord Command Button. The On Click function is as follows:
Private Sub cmdSave_Click()
Dim strWhere As String
Dim stDocName As String
Dim stLinkCriteria As String
Dim cmdSave As String
Dim Resourcetype As String
stDocName = "frmScheduleReosurce"
stLinkCriteria = "[cmdSave]=" & "" & cmdSave & ""
Resourcetype = "qryScheduledResourcetype.Resourcetype"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If IsNull(Me.Resourcetype) Then
MsgBox "You must specify a Resource Type."
strWhere = "qryScheduledResourcetype.Resourcetype = " & Me.Resourcetype
If IsNull(Me.StartDate) _
Or (Not IsDate(Me.StartDate)) Then
MsgBox "You must enter a start date."
strWhere = strWhere & _
" AND EndDate >= #" & Me.StartDate & "#"
If IsNull(Me.EndDate) _
Or (Not IsDate(Me.EndDate)) Then
MsgBox "You must enter an end date."
If Me.StartDate > Me.EndDate Then
MsgBox "Start date cannot be greater than end date."
strWhere = strWhere & _
" AND EndDate <= #" & Me.EndDate & "#"
DoCmd.OpenForm "frmScheduledReosurce", WhereCondition:=strWhere
What I want the function to do is On Click:
1. Open a query that has the four fields listed above.
2. Compare the Resourcetype and StartDate, EndDate fields to see if:
a: if Resourcetype = Null, Then an error message box appears.
b: if StartDate = Null, Then an error message box appears.
c: if EndDate = Null, Then an error message box appears.
d: if StartDate > EndDate, Then an error message box appears.
e: if Resourcetype > 0 and new StartDate and new EndDate = old StartDate and old EndDate, Then an error message box appears. (for example:
Customer Resourcetype StartDate EndDate
Jim Item 1 1-Mar-05 4-Mar-05
In the table above, if I input a new record as follows:
Mike Item 1 2-Mar-05 3-Mar-05
I should get an error message, because Item 1 is already being used by Jim from
1 to 4 March 2005. Mike should not be able to use this Resourcetype until 5 March, 2005. So I need code that searches dates between the start and end dates)
f: if Resourcetype = 0, Then perform save record function.
3. Close the query
When I run the code above, I am getting a Compile Error: method or data member not found.
If you or someone knows what I should do, Please help.
check if all data is entered first
check then for timeoverlaps
Private Sub cmdSave_Click()
const FailMsg = "Please enter a valid "
Dim SQL as string
dim rs as DAO.Recordset
If Not checkValue(Me.cboCustomer, FailMsg & "customer") Then Exit Sub
If Not checkValue(Me.cboResourceType, FailMsg & "resource type") Then Exit Sub
If Not checkValue(Me.cboStartDate, FailMsg & "start date") Then Exit Sub
If Not checkValue(Me.cboEndDate, FailMsg & "end date") Then Exit Sub
'build your SQL using the check posted in the last thread
set rs = currentdb.openrecordset(SQL)
if rs.recordcount > 0 then
MsgBox "There are overlapping records"
Public Function checkValue(CheckControl As Control, FailMessage As String) As Boolean
Dim Result As Boolean
Result = IsNull(CheckControl)
If Result Then
checkValue = Not Result