Hi All,

I'm trying to append to a table. The table layout is as follows:
TableName: WeeklyRpt
Fields:
ID AutoNumber
ReportId Number
ProjectId Number
PM Text
....

The code I'm using to perform this (or trying to perform :-( ):
Private Sub CreateReportSet(RptId As Long)
    Dim myDb As Database
    Dim strSQL As String
    Dim strClosed As String

    If RptId = 0 Then
        Exit Sub
    End If

    Set myDb = CurrentDb

    strClosed = """Closed"""
    strSQL = "INSERT INTO WeeklyRpt (ReportId, ProjectId, PM) " & _
              "SELECT " & CStr(RptId) & " , Projects.ProjectId, Projects.PM " & _
              "FROM Projects " & _
              "WHERE IsNull([Last Report]) " & _
              "OR [Last Report] <> " & strClosed & " " & _
              "OR ([Last Report] = " & strClosed & " AND [Revised Completion Date] > #12/31/2003#);"
    myDb.Execute strSQL
    Set myDb = Nothing
End Sub

Using Debug I can see that the RptId value is set, but I still get the following error message:
Run-time error '3061': Too few parameters. Expected 1

Instead of using the IsNull function, I've also tried "WHERE [Last Report] IS NULL", but that has the same result.

Any ideas?

The rebel without a clue - vandalen@iname.com

I have seen this error before; I think it was related the the regional settings on the computer. Check and make sure it is not set to anything other "English", United States.

I checked the regional settings, but they are as I expected: English(United Kingdom). I would only expect that to have an issue with th edate format, but when building the SQL statement like this, you always have to use US format.

Even more excrutiating is that you can paste the query into the SQL query window and it executes with no problems. proveide, of course, that you put the actual value of RptId instead of the variable.

The rebel without a clue - vandalen@iname.com

Strings are usually passed to the db enclosed in a single quote, so try
instead of

Thanks - tried that, but it has the same result.

The rebel without a clue - vandalen@iname.com

So what does strSQL look like after all the variables have been substituted in? Can you post it here and we'll take a look.

The only variable in this case is RptId, which is defined as a Long. The value when testing it was 30, so strSQL looked like this (as per MsgBox):
"INSERT INTO WeeklyRpt (ReportId, ProjectId, PM) SELECT 30 , Projects.ProjectId, Projects.PM FROM Projects WHERE [Last Report] IS NULL OR [Last Report]<> 'Closed' OR ([Last Report] = 'Closed' AND [Revised Completion Date] > #12/31/2003#);"

The rebel without a clue - vandalen@iname.com

Sorry, can't see anything wrong with that. You're sure you can paste that exact string into the SQL window and it works? If so the only thing I can think of is the date format, as jemacc said earlier. Just for "fun" have you tried using the UK date format?

Changed the date format to UK format & got the same result. Aaargh! Just found it. I changed the tables a while ago and the "PM" field in the Projects table is now called [Current PM]! I changed that and suddenly all's working well again.

Thanks for the help and sorry for wasting your time.

The rebel without a clue - vandalen@iname.com

Check you database for field names under PROJECTS table.

It seems something fishy with either [LAST REPORT] or [Revised Completion Date] field. May be the way it was spelt there could be the problem.

Cheers!

-Vijay G