Here is a replacement piece of code that is a little cleaner & should fix some of the problems.
This code is updated with the assumption that the [Revised Completion Date] field was built in the table to the Date/Time format (not text).
Another suggestion would be to include the tablename or alias on every field being queried (even if just for a comparison).
Private Sub CreateReportSet(RptId As Long)
On Error goto Err_CreateReportSet
Dim myDb As Database
Dim strSQL As String
Dim strClosed As String
If NOT RptId = 0 Then
Set myDb = CurrentDb
strClosed = """Closed"""
strSQL = "INSERT INTO [WeeklyRpt] ([ReportId], [ProjectId], [PM]) " & _
"SELECT " & Cstr(RptId) & " , [Prj].[ProjectId], [Prj].[PM] " & _
"FROM [Projects] AS Prj" & _
"WHERE IsNull([Last Report]) " & _
"OR [Last Report] <> " & strClosed & " " & _
"OR ([Last Report] = " & strClosed & " AND DateDiff("y",[Revised Completion Date], #12/31/2003#) > 0);"
myDb.Execute strSQL
Set myDb = Nothing
End If
Exit_CreateReportSet:
set myDb = Nothing
exit sub
Err_CreateReportSet:
msgbox "Put your CreateReportSet error message here!"
resume Exit_CreateReportSet:
End Sub
|
