Results 1 to 2 of 2
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    Calculating days bewteen previous date and next date in the same date field

    Hi, I have a table containing jobs done on vehicles, inspections, servicing etc. One field is the date the job was done, I need to calculate how many days between the two latest dates for a given vehicle on that field, I have trawled the internet, I did find a solution but kept getting invalid use of null error coming up.

    Here is the code:
    I have replaced my fields where necessary.

    Dim dbCurrent As Database
    Dim rstTest As Recordset

    Dim NoOfRecords As Double, I As Double
    Dim PrevDate As Date, Newdate As Date


    This is the line I get the 'invalid use of null' on

    Newdate = .Fields("newjobdate").Value


    'set up object variables
    Set dbCurrent = CurrentDb()
    Set rstTest = dbCurrent.OpenRecordset("tblTest")

    'work with recordset
    With rstTest
    .MoveLast
    'count the number of records
    NoOfRecords = .RecordCount
    'go to the first record
    .MoveFirst
    'set first date to 0
    .Edit
    !CalcDiff = 0
    .Update
    For I = 1 To NoOfRecords - 1
    'get the previous date
    PrevDate = .Fields("Datesomething").Value
    .MoveNext
    'get the new date
    Newdate = .Fields("Datesomething").Value
    'update the table with the diffrence of the dates
    .Edit
    !CalcDiff = DateDiff("d", PrevDate, Newdate)
    .Update
    Next I
    End With



    'get rid of object variables
    Set rstTest = Nothing
    Set dbCurrent = Nothing

    I know how to calculate days between 2 dates but in the same field I am struggling.

  2. #2
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi all, solved this by myself it works great I was getting an 'invalid use of null' but realised I had blanks in the date field.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-08-2016, 03:22 PM
  2. Replies: 20
    Last Post: 01-13-2015, 02:23 PM
  3. Replies: 1
    Last Post: 09-06-2014, 01:08 PM
  4. Replies: 1
    Last Post: 07-02-2014, 08:48 AM
  5. Replies: 1
    Last Post: 02-12-2013, 03:48 AM

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