Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005

    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:

    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

  2. #2
    Join Date
    Mar 2004
    Berlin, Germany
    don't know if i get you right ..

    first the easy stuff to ensure that StartDate < EndDate you can (should) use a validation rule in the table definition (you can define a message that will pop up in that case)

    the other task is a bit more edgy ..

    to find out whether there are overlapping assets build a query
    SELECT * FROM >AssetTableName< WHERE StartDate<[ActEndDate] AND EndDate>[ActStartDate] AND ResourceType=[ActResourceType]

    replace the [Act..] with the corresponding data from the actual Asset you want to check.

    count the records .. if you get 1 or more results you have an overlap.

    instead of using a save button try to add your code to the beforeUpdate event of the form ..

  3. #3
    Join Date
    Mar 2005

    RE: Need help with a date function for a form


    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."
    Exit Sub
    strWhere = "qryScheduledResourcetype.Resourcetype = " & Me.Resourcetype
    End If
    If IsNull(Me.StartDate) _
    Or (Not IsDate(Me.StartDate)) Then
    MsgBox "You must enter a start date."
    Exit Sub
    strWhere = strWhere & _
    " AND EndDate >= #" & Me.StartDate & "#"
    End If
    If IsNull(Me.EndDate) _
    Or (Not IsDate(Me.EndDate)) Then
    MsgBox "You must enter an end date."
    Exit Sub
    If Me.StartDate > Me.EndDate Then
    MsgBox "Start date cannot be greater than end date."
    Exit Sub
    End If
    strWhere = strWhere & _
    " AND EndDate <= #" & Me.EndDate & "#"
    End If
    DoCmd.OpenForm "frmScheduledReosurce", WhereCondition:=strWhere

    End Sub

    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.

    James Jackson

  4. #4
    Join Date
    Mar 2004
    Berlin, Germany
    just a proposal :

    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"
    exit sub
    End Sub

    Public Function checkValue(CheckControl As Control, FailMessage As String) As Boolean
    Dim Result As Boolean
    Result = IsNull(CheckControl)
    If Result Then
    MsgBox FailMessage
    End If
    checkValue = Not Result
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts