Error of too few parameters in update criteria of a sql statement

[GaryCanuck]

I have this line of code to update certain values in a table. I'm getting the error that "there are too few parameters, expected 2". It's a little tricky but I can't spot the problem. Any help appreciated!

db.Execute "UPDATE [Properties] SET [Discharged?] = YES WHERE [Owner#] = (SELECT [ID] FROM [Owners] WHERE [Municipality] = (SELECT [ID] FROM Municipalities WHERE [ID] = getmunidischarge() AND [Municipalities_Status] = 'NTS Registered')) AND [Properties_Status] = 'Billed' AND [Discharged?] = NO"

 

[dhookom]

I would first replace YES and NO with -1 and 0 assuming Discharged? is a yes/no field. You have us guessing everything about getmunidischarge()...

I recommend code like:

Dim strSQL as String
strSQL = "UPDATE [Properties] SET [Discharged?] = -1 " & _
  "WHERE [Owner#] = (SELECT [ID] FROM [Owners] " & _
  "WHERE [Municipality] = (SELECT [ID] FROM Municipalities " & _
  "WHERE [ID] = getmunidischarge() AND [Municipalities_Status] " & _
  "= 'NTS Registered')) AND [Properties_Status] = 'Billed'" & _
  " AND [Discharged?] = 0"
Debug.Print strSQL
db.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[GaryCanuck]

I gave your code a whirl, thanks kindly for the ideas. Particularly with the boolean values. Your assumption was correct that it is a yes/no field. The "getmunidischarge()" function gets a value from a table, however I've used it in this context successfully before so I don't think it's the root of the problem. For the purposes of debugging it could be replaced by a number between 1-100(that's what it returns). I've tried this without any luck. Still getting the same error message, "too few parameters, expected 2". Any further ideas?

 

[GaryCanuck]

Update, I've gotten a little further with it. The parameter problem was that the field municipalities_status and properties_status should have just been written as [status]. I'm now getting an error that "only one record can be returned by a subquery". Are there ways around this?

 

[GaryCanuck]

I've switched to using an update query to do the same thing. Seems to be working. Thanks for sticking with me!

 

[dhookom]

You might be able to use a totals/group by or TOP 1 query to return only a single record. It's your data so we don't know what it looks like or what you are attempting to do.

Duane
Hook'D on Access
MS Access MVP