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

Reshmi mohankumar

Registered User.
Local time
Today, 19:55
Dec 5, 2017
@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

' 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
Set rs = Nothing
Set db = Nothing

End Function
thank you bro. The DBGuy has done it.


Lifelong Learner
Local time
Today, 06:25
Mar 14, 2017
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.

Users who are viewing this thread

Top Bottom