Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Compare Dates within Same Field

    Hi everyone,

    I need to compare the dates within the same field, so I have one column that has customer numbers, another with what number the shipment is (first, second, third, etc.), a date field, and I need to update the datedifference field between the first shipment and all subsequent shipments. I need to have this be for only when customers are the same.

    CustNo---Shipment---Date---DateDifference
    123---First---1/1/09---0
    123---Second---1/5/09---4
    123---Third---1/10/09---9
    123---Fourth---1/11/09---10

    456---First---2/1/09---0
    456---Second---2/1/09---0
    456---Third---2/6/09---5

    789---First---3/1/09---0
    789---Second---3/2/09---1

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    redesign the data...
    the design is not normalised

    otherwise you are going to have to write a recursive function to extract all the dates which will be horrific
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2009
    Posts
    15
    healdem - Do you have any advice based on the current structure on the best way to go about this? I can provide more information if needed. Thanks!

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    turdfergy, I don't have Access so I can't test this out but you can try something like:
    Code:
    SELECT A.CUSTNO
         , A.SHIPMENT
         , A.DATE
         , DATEDIFF(d,B.DATE, A.DATA) AS DATEDIFFERENCE
    FROM table-name A
           INNER JOIN
         (
          SELECT CUSTNO,MIN(DATE)
          FROM table-name
          GROUP BY CUSTNO
         ) AS B
           ON A.CUSTNO = B.CUSTNO

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this a report or form
    is this a one off ie is only going to appear in the report OR form
    what do you mean by the
    I need to compare the dates within the same field
    whe I read that yesterday I read it as you have the same data in the same column.. ie
    [quote[123---First---1/1/09---0
    123---Second---1/5/09---4
    123---Third---1/10/09---9
    123---Fourth---1/11/09---10[/quote] is one column in the data

    rereading it today it may mean that those are repeating rows.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Below is the code to solve your problem. I have added a an extra field called shipment_no to your table. This field will give a numercial value to the shipment e.g. it will be 1 for Shipment First, 2 for Second. The user or code will assign this value.

    The code does the following
    -Sorts the input by customer number and shipment number.
    - reads the first record and assign 0 to the date difference.
    - reads the next record, if the same customer then the date difference is calculated. Otherwise it assign 0 to the date difference.
    - continues reading and processing the remaining records until the end of the file.

    Sub test_date_diff()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim customer_no_previous As Integer
    Dim customer_no_current As Integer
    Dim date_shipment_previous As Date
    Dim date_shipment_current As Date
    Dim date_diff As Integer
    Dim new_customer As Integer


    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM tbl_shipments ORDER BY CustNo, shipment_no", dbOpenDynaset)

    With rst
    If .RecordCount > 0 Then
    .MoveFirst
    ' store customer no
    customer_no_previous = ![custno]

    ' store date_shipment
    date_shipment_previous = ![shipment_date]

    'set the date difference of the first record to 0
    rst.Edit
    rst("date_difference") = 0
    rst.Update

    ' move to the next record
    .MoveNext

    Do Until .EOF

    customer_no_current = ![custno]

    If customer_no_current = customer_no_previous Then
    date_shipment_current = ![shipment_date]
    date_diff = DateDiff("d", date_shipment_previous, date_shipment_current)

    rst.Edit
    rst("date_difference") = date_diff
    rst.Update

    date_shipment_previous = ![shipment_date]

    Else ' new customer
    rst.Edit
    rst("date_difference") = 0
    rst.Update

    date_shipment_previous = ![shipment_date]
    customer_no_previous = ![custno]
    End If

    .MoveNext

    Loop
    End If
    End With
    End Sub

  7. #7
    Join Date
    Dec 2009
    Posts
    15
    Papa Smurf - I will try this out and be sure to let you know how it works.

    healdem -
    I need to compare the dates within the same field - This means that I have a date field, a shipment number field, customer number field, and I'm trying to compare the dates between the first received package with all subsequent orders.

    So if the First Shipment has a date of 1/1/2009, all subsequent shipments need to subtract from that date if it was shipped to the same customer. This is in my DB as I'm just trying to "set up" a table of data for other people to use. I know it's sort of a calculated field and shouldn't store this, but I don't have many other ideas on what to do for this. Thanks!

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Here is my database, 2003 format, used for my previous post.

    Edit
    Forgot to add you will need to run module mod_test_date_diff.
    Attached Files Attached Files

  9. #9
    Join Date
    Dec 2009
    Posts
    15
    Poppa Smurf - I just realized that I didn't respond to this, but everything works perfectly. I can't thank you enough for the help, I was really stuck and had no ideas.

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Thank you for your reply. Glad to to assist.

  11. #11
    Join Date
    Dec 2009
    Posts
    15
    Poppa Smurf - As a follow up (and addition), is there a way to subtract the difference between any subsequent shipment and the first shipment? Right now, it only subtracts between the previous shipment for that customer. I'm also looking to add date of second shipment, minus date of first....date of third minus date of first.....date of fourth minus date of first. Thanks for the help in advance.

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Here is an updated version as per your request.

    I have added a new field to the table called shipment_orig_diff this will give you the difference between the first and second, first and third etc.

    I have modified various parts of the code using a new variable called date_orig_diff. date_orig_diff keeps a running total from the date of the first shipment and increases for each shipment. When a new customer is found date_orig_diff is reset to zero.
    Attached Files Attached Files

  13. #13
    Join Date
    Dec 2009
    Posts
    15
    Works perfect. Thanks for the help Poppa Smurf.

    Do you know of any places online with some good VBA's basics to start? I can understand what is going on with your code, but wouldn't know where to start at all. Thanks again.

  14. #14
    Join Date
    Dec 2009
    Posts
    15
    Hi Poppa Smurf - Thanks again for the help. I'm trying to use the same logic you sent me in the other example and I'm stuck as it's only running into the first record and stopping. Not sure if you have any ideas. Thanks!

    Option Compare Database
    Sub Up_Down_WE()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim customer_no_previous As String
    Dim customer_no_current As String
    Dim device_type_previous As String
    Dim device_type_current As String

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM ShipmentData ORDER BY [Ship-To Customer], Shipment_No", dbOpenDynaset)

    With rst
    If .RecordCount > 0 Then
    .MoveFirst

    ' Store Customer Number
    customer_no_previous = ![[Ship-To Customer]]

    ' Set The First Shipment to DF
    rst.Edit
    rst("UpDownWE") = "Direct Fulfillment"
    rst.Update

    ' Move to Next Record
    .MoveNext

    Do Until .EOF

    customer_no_current = ![[Ship-To Customer]]
    device_type_current = ![DeviceRanking]

    If customer_no_current = customer_no_previous And device_type_current = device_type_previous Then
    rst.Edit
    rst("UpDownWE") = "Warranty Exchange"
    rst.Update

    ElseIf customer_no_current = customer_no_previous And device_type_current = "2" And device_type_previous = "1" Then
    rst.Edit
    rst("UpDownWE") = "Upgrade"
    rst.Update

    ElseIf customer_no_current = customer_no_previous And device_type_current = "1" And device_type_previous = "2" Then
    rst.Edit
    rst("UpDownWE") = "Downgrade"
    rst.Update

    End If

    .MoveNext

    Loop
    End If
    End With
    End Sub

  15. #15
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I had a quick look at your code. You are not updating customer_no_previous when there is a change in Ship-To Customer.

    If you like we can continue this "off forum". Please send me a zipped copy of your database to the email address below and we will use this to assist you in learning VBA.

Posting Permissions

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