Displaying non ASCII characters in a recordset

[toothpaste]

I have the following VB code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String
Dim i As Integer
    
strSQL = "SELECT " _
& "DLookUp(" & Chr(34) & "'[first_name] & ' ' & [last_name]'" & Chr(34) & ",'[activeGuides]','[ID]=' & [activities].[guide]) AS guide," _
& "activities.garden, " _
& "activities.activityDate, " _
& "activities.comment " _
& "FROM activities; "


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
    
    If Not rs.BOF And Not rs.EOF Then
        
        For i = LBound(myArray) To UBound(myArray)
            
            If myArray(i, 1) Then
                rs.Filter = "[activities.activityDate]=" & myArray(i, 0)
                
                Set rsFiltered = rs.OpenRecordset
                
                Do While (Not rsFiltered.EOF)
                    
                    myArray(i, 2) = myArray(i, 2) & vbNewLine _
                    & rsFiltered!guide & " " _     <-- Buggy line
                    & rsFiltered!garden & " " _
                    & rsFiltered!activityDate & " " _
                    & rsFiltered!comment
                    
                    rsFiltered.MoveNext
                Loop
            
            End If
        Next i
        
    End If
    
    rsFiltered.Close
    rs.Close


Set rsFiltered = Nothing
Set rs = Nothing
Set db = Nothing

the problem is that "rsFiltered!guide" contains unicode characters and causes the program to crash (removing the line works perfectly)
Is there a way to extract the unicode data and display it in a textbox?



Thanks!

[ssanfu]

You have this line:

For i = LBound(myArray) To UBound(myArray)

Where is it declared?

I think the error is caused by the DLookup function. It looks like there are too many single quotes/double quotes.

Since you are looking up the first/last name in "activeguides" where activeguides.ID = activities.guide, is it possible to use a query with both tables in it? (are the tables linked?)

Something like

SELECT
[first_name] & ' ' & [last_name] AS Guide, 
activities.garden, 
activities.activityDate, 
activities.comment 
FROM activities Inner Join activeGuides On activities.guide = activeGuides.ID

(this is as close as I can come without seeing the tables.....)

[toothpaste]

Thanks! it did fix it

[ssanfu]

Great! I'll mark this solved......