Hello all,

I have a problem with a sql statement in vba here is my code and I do
not know why because the same query in sql server works file and the
params strRoom is well get through my function

The sub that call this function i do like this:
strRoom = GetroomNumber(cndb, strRoomCode)

Option Explicit

' Description : Get Currency from Index
' Params : the strAssetCode as argument (index InternalCode)
' Returns : an array as String
' Author : RI
' Last Update : 11.05.06


Public Function GetRoomNumber(ByVal cndb As ADODB.Connection,
strRoomCode As String) As String
On Error GoTo GetRoomNumber_Err

Dim strSQL As String 'String for SQL statement
Dim rsroom As ADODB.Recordset 'Recorset


'Set a new recorset
Set rsRoom = New ADODB.Recordset


'SQL statement
strSQL = "SELECT CURRENCY FROM INDEXCURRENCY WHERE INTERNALCODE= '" &
strRoomCode & " '"


Debug.Print strSQL 'I can see the query and if I use the SQL it works

'Recorset and Connection
rsRoom.ActiveConnection = cndb
rsRoom.Open strSQL


GetRoomNumber = rsRoom.Fields(1).Value 'GetRoom = room number

'close the recorset
rsRoom.Close
Set rsRoom = Nothing
Exit Function

GetRoomNumber_Err:
GetRoomNumber = CVErr(xlErrNA)
Exit Function

End Function