Data Tyep Mismatch - Run Time Error

[Guest]

I have craeted the following module to define a function in order to run in a query. When I run the query I get a runtime error that states data type mismatch

======For DAO uncomment next 4 lines======
'====== comment out ADO below ======
'Dim db As DAO.Databas
'Dim rs As DAO.Recordse
'Set db = CurrentD
'Set rs = db.OpenRecordset(pstrSQL

'======For ADO uncomment next two lines====
'====== comment out DAO above =====
Dim rs As New ADODB.Recordse
rs.Open pstrSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimisti
Dim strConcat As String 'build return strin
With r
If Not .EOF The
.MoveFirs
Do While Not .EO
strConcat = strConcat &
.Fields(0) & pstrDeli
.MoveNex
Loo
End I
.Clos
End Wit
Set rs = Nothin
'====== uncomment next line for DAO =======
'Set db = Nothin
If Len(strConcat) > 0 The
strConcat = Left(strConcat,
Len(strConcat) - Len(pstrDelim)
End I
Concatenate = strConca
End Functio

What can I do to make this work. I am looking to concatinate multiple records into one.

 

[[MVP] S.Clark]

Which line is causing the problem? Is the error in the code or in the
query?

Datatype mismatches are relatively easy to solve. Examine the datatype of
anything being compared, and look for something where a number is compared
to a string, or anything is compared to a Null.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have craeted the following module to define a function in order to run

in a query. When I run the query I get a runtime error that states data
type mismatch.

======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

What can I do to make this work. I am looking to concatinate multiple

records into one.