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.