Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Unanswered: Compare two date Fields

    Hi guys
    I'd like to compare two Fields (Wich Stores Date) and than Add To a third Filed the Max(Field1,Field2)
    this had looked to my a simple thing to do, but unfortunately working with date is more difficult than I'd expected;
    I've tryed something like this:

    Sub pi()
    Dim rst As DAO.Recordset
    Dim dif As Long
    DTCMPT As DateTime
    DTDEBEF As DateTime


    Set rst = CurrentDb.OpenRecordset("Step1", dbOpenDynaset)
    With rst
    Do Until .EOF
    dif = DateDiff(DTCMPT, DTDEBEF)
    If dif <= 0 Then
    .Edit
    !MDATE = !DTCMPT
    .Update
    Else
    .Edit
    !MDATE = !DTDEBEF
    .Update
    End If
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing
    End Sub



    End Sub

    but of course it doesen't work becouse the syntax is wrong. How can I adjust this to work????
    Thank you all for your answares!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is incorrect:
    Code:
    dif = DateDiff(DTCMPT, DTDEBEF)
    The proper syntax for DataDiff is:
    Code:
    DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
    See Access Help System.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    How can I declare that DTCMP , DTBEDEF, MDATE are the fields that I want to use to compare and update????
    Last edited by jsirico; 01-26-12 at 13:02.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    Sub pi()
    
        Dim rst As DAO.Recordset
        Dim dif As Long
    '
    '    Dim DTCMPT As Date     ' Dim was missing for both lines,
    '    Dim DTDEBEF As Date    ' the variable type is Date, not DateTime
                                ' Both lines are useless: DTCMPT  and DTDEBEF are not used.
    '  
        Set rst = CurrentDb.OpenRecordset("Step1", dbOpenDynaset)
        With rst
            Do Until .EOF
                .Edit
                dif = DateDiff("d", !DTCMPT, !DTDEBEF)
                If dif <= 0 Then
                    !MDATE = !DTCMPT
                Else
                    !MDATE = !DTDEBEF
                End If
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    54
    Thank you It Works Great

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in actual fact you can take advantage of the way that Access / JET stores dates whichis numeric the most significiant digits represenbt the number of days since (IIRC 01 Jan 1900), and the decimal = proportion of days eg .5 = 12:00, .75 = 18:00, 0.0416666666666667 an hour)

    so you could run two queries
    eg
    update mytable set mydatecolumn = date1 where date1 >= date2
    update mytable set mydatecolumn = date2 where date2 >date1

    or
    update mytable set mydatecolumn = iif(date1>= date2, date1, date2)


    mind you it does beg the question why you'd want to store such derived data as you can always find the correct date in the query by using the iif clause above. storing derived data is a no no..
    why?
    what happens if later on somebody changes the values of one or more date columns, then you have to rerun your process to set the new date.

    there are times when you can and should store what at first glance seems derived data. ferinstance on an order, the order details probably should list the price the goods were sold at, the prevailing sales tax rate (or value), the order header should record the actual customer. so you do not want to change those items if say your sales tax rate goes up, the supplier changes or changes name and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2012
    Posts
    54
    Thank you sooooo much!!!!
    GrazieMille!!!!

Posting Permissions

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