The frustrating 3061 Too Few Parameters error 1

[fneily]

Sirs or Madams: I have a table with two fields - partno, desc. Desc can span more than one record, eg. partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field. I found the following code.

First the query looks like this:
SELECT [partno], fConcatFld("parttable","partno","desc","string",[partno]) AS descs
FROM parttable
GROUP BY [partno];

The fConcatFld function looks like this:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb

loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = " & vForFldVal & " ;"

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

At the Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapShot) I get the old 3061 error,Too few parameters. Expected 1.

I put a stop at this line and in the immediate pane I printed the loSQL which follows:

? loSQL
SELECT [desc] FROM [parttable] WHERE [partno] = abcd ;

Now without the WHERE part of the SQL, it does concatenate everything. I put the WHERE in and it messes up. Can anyone see the problem with the OpenRecordset statement?

Thanks
neil

 

[Zathras]

If abcd isn't a column name it should be 'abcd' (quotes).

(Unless [partno] is numeric in which case I don't know why you would have represented it with letters in your post.)

 

[fneily]

Zathras

Good eyes! Changed the code to put ' ' around the field name, changed the last line to read -1 instead of -2, made sure my DAO library is referenced first and it worked.

Thanks again.

Neil