Originally Posted by
pbaldy
You missed this:
As is, your insert SQL is incomplete. It would either be:
INSET INTO...
VALUES(...)
or
INSET INTO...
SELECT...FROM...
As you have it, Access has no idea what to insert.
I know this thread is literally two; i posted twice same day on Forum trying to achieve two different objectives from the same code the code checks the difference between dates and Returns a value while deleting and appending the table at the same time I worked this code before. in another thread [table design datediff two date fields rows_down consecutively] am trying to do the same thing but with two date fields and not one as this thread.... However the issue in this thread is the runtime error in macros this is the code....
Code:
Function tblReturnedDays()
Dim db As Database, rst As Recordset
Dim StrSQL As String
Dim PrevID As Long
Dim PrevDate As Date
StrSQL = "DELETE tblReturnedDays.* FROM tblReturnedDays;"
DoCmd.RunSQL (StrSQL)
StrSQL = "INSERT INTO tblReturnedDays ( Inm_ID, Name, OffencesName, EntryDate )" _
& " SELECT tblInmates.Inm_ID, [LastName] & ', ' & [FirstName] AS Name, tblOffences.OffencesName, [tblInmOff Jun].EntryDate" _
& " FROM tblOffences INNER JOIN (tblInmates INNER JOIN [tblInmOff Jun] ON tblInmates.Inm_ID = [tblInmOff Jun].Inm_pd) ON tblOffences.Off_ID = [tblInmOff Jun].Off_pd " _
& " ORDER BY tblInmates.Inm_ID, [tblInmOff Jun].EntryDate;"
DoCmd.RunSQL (StrSQL)
Set db = CurrentDb
Set rst = db.OpenRecordset("tblReturnedDays")
rst.MoveFirst
Do
PrevID = rst!Inm_ID
PrevDate = rst!EntryDate
rst.MoveNext
If rst.EOF Then
Exit Do
End If
If rst!Inm_ID = PrevID Then
rst.Edit
rst!DaysReturned = rst!EntryDate
rst.Update
End If
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Function