Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating time between records (A2k)

    I have a table set up as follows:


    <table border=1><td>ID</td><td>Date</td><td># Days</td><td>Current Location</td><td>1</td><td>1 July 05</td><td><span style="background-color: #FFFF00; color: #000000; font-weight: bold"> 5</span hi></td><td>Big Boss Man</td><td>1</td><td>6 July 05</td><td><span style="background-color: #FFFF00; color: #000000; font-weight: bold"> 1</span hi></td><td>Little Worker Guy</td><td>1</td><td>7 July 05</td><td><span style="background-color: #FFFF00; color: #000000; font-weight: bold"> </span hi></td><td>Post Office</td></table>

    Records are added to this table after a field is updated. What I would like to do is calculate the time between when an object arrived at a location (Date) and the next time a record is added. The highlighted numbers are what *should* end up in the field. The ID field is the primary key. We're trying to be able to point out where projects get slowed down. I hope my explanation is clear enough.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating time between records (A2k)

    To save from making yet another post, I'll ask my next question here. I want to requery an object (lstPackageHistory) on my main form after an object on a subform is updated. For the life of me I can't figure out how to refer to the object. The list box resides on Forms.frmCareerPlanner.lstPackageHistory. I don't know how to get it to requery after the object on my subform is updated. Next thing. The list box directly coincides with the calculation, as I would like to show the time in the list box. The list box's SQL is as follows:

    SELECT tblPackageTracker.SSN, tblPackageTracker.LOCATION, tblPackageTracker.LOCATION_DATE, tblPackageTracker.COMMENTS FROM tblPackageTracker WHERE (((tblPackageTracker.SSN)=FORMS!FRMCAREERPLANNER!S SN));

    I'd like to add a column with the number of days at each location in there, but I don't know how. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculating time between records (A2k)

    Don't you mean:

    NumberOfDays: DMin("[Date]","tblData","[ID] = " & [ID] & " And [Date] > #" & Format([Date],"mm/dd/yyyy") & "#")-[Date]

    I couldn't find any reference to DateIn.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    1) In the After Update event of the object on the subform:

    Me.Parent.lstPackageHistory.Requery

    Me refers to the form running the code, i.e. the subform, and Parent to the main form that contains the subform.

    2) Create a query based on tblPackageTracker. Add SSN, Location, Location_Date and Comments. Set the criteria for SSN to

    Forms!frmCareerPlanner!SSN

    Add a calculated column

    NumberOfDays: DMin("[Location_Date]","tblPackageTracker","[SSN] = " & [SSN] & " And [Location_Date] > #" & Format([Location_Date],"mm/dd/yyyy") & "#")-[Location_Date]

    Save this query as (say) qryPackageTracker. Set the Row Source of the list box to qryPackageTracker. Don't forget to increase the Column Count and to specify a column width for the new column.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    Yep, sorry, that was the name of the field in my test database, I forgot to replace that instance.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    Typo corrected by HansV (thanks, Patt!)

    Since the number of days is a derived value, you shouldn't store it in the table, but calculate it in a query based on the table. Say that your table is named tblData; the calculated column would look like this:

    NumberOfDays: DMin("[Date]","tblData","[ID] = " & [ID] & " And [Date] > #" & Format([Date],"mm/dd/yyyy") & "#")-[Date]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    If SSN is a text field, try

    NumberOfDays: DMin("[Location_Date]","tblPackageTracker","[SSN] = " & Chr(34) & [SSN] & Chr(34) & " And [Location_Date] > #" & Format([Location_Date],"mm/dd/yyyy") & "#")-[Location_Date]

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating time between records (A2k)

    Well, the listbox *should* show the most recent addition, because the code adds a new record to the table that the listbox queries, but it doesn't show the new record.

    I followed your steps for the query (which, by the way, is amazing.... <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>how do you remember all of that?!) but when I select a record on the form and the query runs, I get vbExclamation "Data type mismatch in criteria expression". I get the error for each record that is found in the query.

    I forgot to mention, my date fields are formatted yyyymmdd, however I tried changing the calculated field, (with no success). I now get the error
    Syntax error in date in query expression '[SSN] = 000000000 And [Location_Date] > #20050724#'.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    You MUST use mm/dd/yyyy format in SQL.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating time between records (A2k)

    Hans, that works now, thank you. How can I format the field to only show whole POSITIVE numbers? If it moves more than once in a day, it will show a - number, I'd rather it show a 0. My list box still doesn't requery. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    Set the format to <code>0;0</code>
    This tells Access to display negative values as 0 (I don't understand how you can get negative results, by the way)

    If your code runs in the After Update event of a control on the subform, the record hasn't been saved yet - that happens when the user moves to another record. So perhaps the code needs to go into the
    After Update event of the subform instead of the control.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating time between records (A2k)

    The formatting works (I get negative values if the location changes more than once per day).

    The code in the after update of the event of the object works if I change records. Is there a way to refresh the listbox immediately?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating time between records (A2k)

    The Row Source of the list box only sees the 'saved' records. The only way to update the list box immediately would be to save the record in the subform (using RunCommand acCmdSaveRecord) each time the relevant control on the subform is updated, but that is probably undesirable - the user would lose undo capability. I would live with updating the combo box in the After Update event of the subform.

Posting Permissions

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