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

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
13,225
Hi. What happens if you step through your code? As an alternate, you could also take a look at this simple function.

 

Isaac

Lifelong Learner
Local time
Today, 06:36
Joined
Mar 14, 2017
Messages
2,769
But i didn't find the way to execute this
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.
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
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.
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 ".
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
Hi. I don't understand your question. Did you click on the link I posted above?
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)"
 

Isaac

Lifelong Learner
Local time
Today, 06:36
Joined
Mar 14, 2017
Messages
2,769
Are you sure it's not really debugging and highlighting this line?
Double check again ...

Code:
Set rs = qdf.OpenRecordset(ssql, 1)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
13,225
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)"
Hi. Can you show me exactly how you tried to call the SimpleCSV() function? Thanks.
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
Hi. I don't understand your question. Did you click on the link I posted above?
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
13,225
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
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.
 

Isaac

Lifelong Learner
Local time
Today, 06:36
Joined
Mar 14, 2017
Messages
2,769
@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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
13,225
Me.txtnums.Value = SimpleCSV("Select smsnumber from Qry_sms_numbers")
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.
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
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.


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));
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:36
Joined
Oct 29, 2018
Messages
13,225
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));
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.
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
@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
i got error 3061, 'too few parameters- Expected 1 placed at same position
 

Reshmi mohankumar

Registered User.
Local time
Today, 20:06
Joined
Dec 5, 2017
Messages
91
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.
i finally got , but bit change in your where clause i put in to Eval('[FORMS]![Frm_SMS]![CBOCLS]')

thank you so much.
 

Users who are viewing this thread

Top Bottom