Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 22nd, 2004, 12:15 PM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to morne
Default SQL Update query

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
 
Old April 22nd, 2004, 01:50 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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.


 
Old April 23rd, 2004, 03:46 AM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to morne
Default

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
 
Old April 23rd, 2004, 04:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Strings are usually passed to the db enclosed in a single quote, so try
Code:
strClosed = "'Closed'"
instead of
Code:
strClosed = """Closed"""
 
Old April 23rd, 2004, 04:26 AM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to morne
Default

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

The rebel without a clue - vandalen@iname.com
 
Old April 23rd, 2004, 04:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
 
Old April 23rd, 2004, 04:58 AM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to morne
Default

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
 
Old April 23rd, 2004, 05:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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?
 
Old April 23rd, 2004, 05:20 AM
Registered User
 
Join Date: Apr 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to morne
Default

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
 
Old April 23rd, 2004, 05:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Update query (error message) Neal SQL Server 2000 1 August 3rd, 2007 08:57 PM
Update table using SQL query from form pater53 Access VBA 4 January 24th, 2007 11:24 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
Update query equivalent in SQL Mitch SQL Server 2000 5 May 5th, 2005 05:14 AM
update query? robb Access 4 May 19th, 2004 04:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.