Results 1 to 2 of 2
  1. #1
    BadKat is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2013
    Posts
    1

    Identify date in previous record and calculate date difference in days

    Hi I am trying to work out if I can set the default value of a field called "NumDays" to be the difference between the "VisitDate" entered in that record instance and the "VisitDate" entered in a previous record for the same person where that record's field "OriginalStartDate" is True. I'm not sure what to do if there isn't one or if the record being entered is the first record for a person? Alternately, when adding a new record and updating the field "VisitDate" I want this condition to run which I suppose I can do on the Update Event. Anyone know how to do the code for calculating this? In my head so far it should go along the lines of:

    Upon Update VisitDate,
    if number of records for person is more than 0 then
    if date is valid visitdate i.e. Mon-Fri date then NumDays = VisitDate - VisitDate from record where OriginalStartDate Is True
    else msg "visitdate not a valid date selection"

    Any assistance with correct coding to identify VisitDate from correct record and also how to calculate number of days between dates will be great - it's been years since I worked in Access and with two young babies am rather rusty

  2. #2
    qa12dx is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    india
    Posts
    112
    use the dmax function to see the last date. criteria will be customer ID and <> current record ID

    then use datediff function to see difference in dates in whatever format u need.
    see the datediff example i made for a user thread in https://www.accessforums.net/queries...een-32406.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-28-2013, 05:21 PM
  2. Replies: 3
    Last Post: 06-19-2012, 10:42 PM
  3. Subtract From Previous Record Using a Date
    By txrules in forum Queries
    Replies: 1
    Last Post: 12-30-2010, 02:10 AM
  4. Replies: 1
    Last Post: 11-12-2010, 01:16 AM
  5. Populate date from previous record
    By Lianes in forum Access
    Replies: 3
    Last Post: 09-19-2009, 03:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums