Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Unanswered: How to compare current record values with previous record values in a same table

    How to compare current record values with previous record values in a same table
    i have 2 tables
    1)vehicle details ---it has vehicle no and name..................
    2)insurance--- it has 4 fields vehicle no,amount,from date and to date.
    vehicle no is the foreign key
    ex: 1st record:tata 123,10,000,4/23/2011,4/22/2012
    the second record for the same vehicle should not accept same period,it should allow from date after "4/22/2012"
    can anyone tell me whats the solution for this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "previous record" does not have any certain meaning. It can vary according to the natural order of insertion if there is no index defined, to the primary key or to another index, to the sort order applied to the data set, etc.

    1. If you want to ensure that 2 values cannot be the same in several rows, the best you can do is to creater a unique index that can be made of several columns.

    2. You could use a DLookUp() or DCount() function to check for the condition before inserting new rows or updating existing ones:
    Code:
    IF DCount("[vehicle no]", "insurance", "[vehicle no] ='tata 123' AND [to date] > #4/22/2012#) = 0 then
        ' INSERT OK --> row does not exist.
    End If
    3. If a RecordSet is already open on the required data set (in case of a form for instance), you could use:
    Code:
    Dim rst As DAO.RecordSet
    Set rst = Me.RecordSetClone
    rst.FindFirst "[vehicle no] ='tata 123' AND [to date] > #4/22/2012#"
    If rst.Nomatch = True Then
        ' INSERT OK --> row does not exist.
    End If
    Of course, in both case you would replace tata 123 and 4/22/2010 by the appropriate values that would usually be some properly formated variables. E.g.:
    Code:
    rst.FindFirst "[vehicle no] ='" & Me.[vehicle no].Value & "' AND [to date] > #" & Format(NewStartDate, "mm/dd/yyyy") & "#"
    Have a nice day!

Posting Permissions

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