Solved Error '3421' , Conversion data type (1 Viewer)

[Reshmi mohankumar]

@Reshmi mohankumar
use this code instead:

Public Function RSFieldAsSeparatedString(ByVal fieldName As String, _
ByVal tableOrQueryName As String, _
Optional ByVal delimiter As String = "," _
) As String

' Paramter description
' fieldName = Is the name of the field containing the values
' we want in our comma separated string
' tableOrQueryName = Is the name of table or query containing the column
' criteria = The criteria to filter the data
' sortBy = An optional sort expression to sort the data
' delimiter = The delimiter used to separate the values. It defaults
' to a comma and a blank, but you can use anything you
' like there


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim ssql As String
'Dim whereCondition As String
Dim retVal As String

Set db = Application.CurrentDb
Set qdf = db.QueryDefs(tableOrQueryName)
' If there where any criteria passed to the function, we build a WHERE-condition for SQL
' If Len(criteria) > 0 Then
'whereCondition = " WHERE " & criteria
'End If



' building the complete SQL string
ssql = "Select " & fieldName & " from " & tableOrQueryName

' opening a recordset
'Set rs = qdf.OpenRecordset(ssql, 1)
Set rs = db.OpenRecordset(ssql)

Do Until rs.EOF
' here we are looping through the records and, if the value is not NULL,
' concatenate the field value of each record with the delimiter
If Not IsNull(rs.Fields(0).Value) Then
retVal = retVal & Nz(rs.Fields(0).Value, "") & delimiter
End If
rs.MoveNext
Loop

' we cut away the last delimiter
retVal = Left(retVal, Len(retVal) - Len(delimiter))

' setting the return value of the function
RSFieldAsSeparatedString = retVal

' cleaning up our objects
rs.Close
Set rs = Nothing
Set db = Nothing

End Function

thank you bro. The DBGuy has done it.

 

[Isaac]

i got error 3061, 'too few parameters- Expected 1 placed at same position

It all makes sense.

The problem with your original code as-written, (a problem not dependent on your specific situation), was this line:

Set rs = qdf.OpenRecordset(ssql, 1)

Because I created a simple query, qRY_SMS_NUMBERS, with a column smsnumber, and the code still didn't work, despite the fact that mine wasn't a parameter query. It didn't work because literal SQL isn't something that can be passed to QueryDef.OpenRecordset.
I fixed it by changing that line to:

Set rs = db.OpenRecordset(ssql)

...and it worked perfectly.

However dbGuy is also correct, in that he fleshed out another problem - that you are using a parameter query as well, which it looks like he is helping you out with (you are in good hands!).

Just so you understand both problems including the error in orig. code, which presents itself regardless of context.