Solved Error '3421' , Conversion data type

Reshmi mohankumar

Reshmi mohankumar

Registered User.
Local time
Today, 06:47
Joined
Dec 5, 2017
Messages
101
i tried to run this procedure for the column values to convert to row values and separated by ",", i had a code below. But i didn't find the way to execute this


<code>
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)
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
</code>


here is my call code
<code>
Me.txtnums.Value = RSFieldAsSeparatedString("smsnumber", "qRY_SMS_NUMBERS")
</code>

here 'smsnumbers' datatype is shorttext in linked table, But i insert number values there
 
Hi. What happens if you step through your code? As an alternate, you could also take a look at this simple function.

 
Reshmi mohankumar said:
But i didn't find the way to execute this
Click to expand...
I don't understand specifically the problem you're having. Can you explain more in detail?
Where/when does the code Me.txtnums.Value = RSFieldAsSeparatedString("smsnumber", "qRY_SMS_NUMBERS") get called? A form load, or where?
What happens? Do you get an error? If so,which one? Please be more specific so help can be provided.
 
Isaac said:
I don't understand specifically the problem you're having. Can you explain more in detail?
Where/when does the code Me.txtnums.Value = RSFieldAsSeparatedString("smsnumber", "qRY_SMS_NUMBERS") get called? A form load, or where?
What happens? Do you get an error? If so,which one? Please be more specific so help can be provided.
Click to expand...
it called from command button on form. txtnums is a textbox on the same form.
when i click it shows error massage as type conversion and debug window highlighted at " ssql = "Select " & fieldName & " from " & tableOrQueryName ".
 
theDBguy said:
Hi. I don't understand your question. Did you click on the link I posted above?
Click to expand...
hi DBGuy thanks for link. and i copied that and passed a sql string through paramater and i got error 3061, 'too few parameters- Expected 1', then placed at "Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)"
 
Are you sure it's not really debugging and highlighting this line?
Double check again ...

Code: 
Set rs = qdf.OpenRecordset(ssql, 1)
 
Reshmi mohankumar said:
hi DBGuy thanks for link. and i copied that and passed a sql string through paramater and i got error 3061, 'too few parameters- Expected 1', then placed at "Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)"
Click to expand...
Hi. Can you show me exactly how you tried to call the SimpleCSV() function? Thanks.
 
theDBguy said:
Hi. I don't understand your question. Did you click on the link I posted above?
Click to expand...
After i changed to

db.OpenRecordset(strSQL, 1)" the below error i got.
actually i had linked tables , from then i created a the query "Qry_sms_numbers".. Is this the correct way?to get data?



err.JPG
 
Reshmi mohankumar said:
After i changed to

db.OpenRecordset(strSQL, 1)" the below error i got.
actually i had linked tables , from then i created a the query "Qry_sms_numbers".. Is this the correct way?to get data?



View attachment 84987
Click to expand...
Hi. You just quoted me, which makes it look like you're responding to my post. But the content of your post looks like you're actually responding to Isaac. Which is it? I asked you to show me how you used the SimpleCSV() function.
 
@Reshmi mohankumar
use this code instead:

Code: 
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
 
Reshmi mohankumar said:
Me.txtnums.Value = SimpleCSV("Select smsnumber from Qry_sms_numbers")
Click to expand...
Okay, that's what I was expecting. This tells me Qry_sms_numbers is a parameter query. Can you show us the SQL statement for that query? Thanks.
 
theDBguy said:
Okay, that's what I was expecting. This tells me Qry_sms_numbers is a parameter query. Can you show us the SQL statement for that query? Thanks.
Click to expand...


SELECT Tbl_Student.Contact1 AS smsnumber, Tbl_class.Class, Tbl_Student.Active
FROM Tbl_class INNER JOIN (Tbl_active_class INNER JOIN Tbl_Student ON Tbl_active_class.StudentID = Tbl_Student.StudentID) ON Tbl_class.ClassID = Tbl_active_class.Classid
WHERE (((Tbl_class.Class)=[FORMS]![Frm_SMS]![CBOCLS]) AND ((Tbl_Student.Active)=True));
 
Reshmi mohankumar said:
SELECT Tbl_Student.Contact1 AS smsnumber, Tbl_class.Class, Tbl_Student.Active
FROM Tbl_class INNER JOIN (Tbl_active_class INNER JOIN Tbl_Student ON Tbl_active_class.StudentID = Tbl_Student.StudentID) ON Tbl_class.ClassID = Tbl_active_class.Classid
WHERE (((Tbl_class.Class)=[FORMS]![Frm_SMS]![CBOCLS]) AND ((Tbl_Student.Active)=True));
Click to expand...
Thanks. Try changing it to this:
Rich (BB code): 
SELECT Tbl_Student.Contact1 AS smsnumber, Tbl_class.Class, Tbl_Student.Active
FROM Tbl_class INNER JOIN (Tbl_active_class INNER JOIN Tbl_Student ON Tbl_active_class.StudentID = Tbl_Student.StudentID) ON Tbl_class.ClassID = Tbl_active_class.Classid
WHERE (((Tbl_class.Class)=Eval("[FORMS]![Frm_SMS]![CBOCLS]")) AND ((Tbl_Student.Active)=True));
And then try SimpleCSV() again.
 
Isaac said:
@Reshmi mohankumar
use this code instead:

Code: 
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
Click to expand...
i got error 3061, 'too few parameters- Expected 1 placed at same position
 
theDBguy said:
Thanks. Try changing it to this:
Rich (BB code): 
SELECT Tbl_Student.Contact1 AS smsnumber, Tbl_class.Class, Tbl_Student.Active
FROM Tbl_class INNER JOIN (Tbl_active_class INNER JOIN Tbl_Student ON Tbl_active_class.StudentID = Tbl_Student.StudentID) ON Tbl_class.ClassID = Tbl_active_class.Classid
WHERE (((Tbl_class.Class)=Eval("[FORMS]![Frm_SMS]![CBOCLS]")) AND ((Tbl_Student.Active)=True));
And then try SimpleCSV() again.
Click to expand...
i finally got , but bit change in your where clause i put in to Eval('[FORMS]![Frm_SMS]![CBOCLS]')

thank you so much.
 
You must log in or register to reply here.

Similar threads

B
Caller ID via TAPI (tapi32.dll) from phone phone PBX
Replies
1
Views
252
Uncle Gizmo
Uncle Gizmo
Gasman
32bit and 64bit?
Replies
4
Views
219
isladogs
isladogs
J
Search for possible declarations
Replies
9
Views
358
sonic8
S
U
Export blob OLE Object Field Item instead of Attachment Field
Replies
9
Views
562
jdraw
jdraw
avincent61
  • Question Question
DateAdd with DMax +1, not updating table data
Replies
14
Views
285
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom