edit function to accommdate integer

[shank]

I have the following query and below function. I get an error "Data Type
mismatch in criteria expression". I'm sure it's related to the [DiscNo]
field being an integer. How can I edit the below fuction to accommodate that
field as an integer?
thanks!
=========================================
INSERT INTO TempConCat ( DiscNo, MemoField )
SELECT FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle & '
.... ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo ='" &
FinalSongs.DiscNo & "' ORDER BY TrackNo",Chr(13) & Chr(10)) AS Titles
FROM ProductionSchedule INNER JOIN FinalSongs ON ProductionSchedule.DiscNo =
FinalSongs.DiscNo
GROUP BY FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle &
' ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo ='" &
FinalSongs.DiscNo & "' ORDER BY TrackNo",Chr(13) & Chr(10))
ORDER BY FinalSongs.DiscNo;
=========================================

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======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

 

[Duane Hookom]

I removed four apostrophes (single quotes).
INSERT INTO TempConCat ( DiscNo, MemoField )
SELECT FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle & '
.... ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo =" &
FinalSongs.DiscNo & " ORDER BY TrackNo",Chr(13) & Chr(10)) AS Titles
FROM ProductionSchedule INNER JOIN FinalSongs ON ProductionSchedule.DiscNo =
FinalSongs.DiscNo
GROUP BY FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle &
' ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo =" &
FinalSongs.DiscNo & " ORDER BY TrackNo",Chr(13) & Chr(10))
ORDER BY FinalSongs.DiscNo;


--
Duane Hookom
MS Access MVP

I have the following query and below function. I get an error "Data Type
mismatch in criteria expression". I'm sure it's related to the [DiscNo]
field being an integer. How can I edit the below fuction to accommodate
that field as an integer?
thanks!
=========================================
INSERT INTO TempConCat ( DiscNo, MemoField )
SELECT FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle &
' ... ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo ='" &
FinalSongs.DiscNo & "' ORDER BY TrackNo",Chr(13) & Chr(10)) AS Titles
FROM ProductionSchedule INNER JOIN FinalSongs ON ProductionSchedule.DiscNo
= FinalSongs.DiscNo
GROUP BY FinalSongs.DiscNo, Concatenate("SELECT TrackNo & '. ' & SongTitle
& ' ' & SongArtist FROM FinalSongs WHERE FinalSongs.DiscNo ='" &
FinalSongs.DiscNo & "' ORDER BY TrackNo",Chr(13) & Chr(10))
ORDER BY FinalSongs.DiscNo;
=========================================

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======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