jjobrien03
New Member
- Joined
- Sep 2, 2014
- Messages
- 41
I have the below User Defined Function to pull from a SQL database. I have three different types of "UniqueIdentifierType" that could be used (name, numeric code, non-numeric code). When my UniqueIdentifier is all numeric, the functions works as expected. However, it does not handle the other two non-numeric types. I think it has to do with my defined variable type, any ideas?
Resulting SQL Command: ("SELECT " & Output & " FROM DATABASE WHERE " & UniqueIdentifierType & " = " & UniqueIdentifier & ";")
Resulting SQL Command: ("SELECT " & Output & " FROM DATABASE WHERE " & UniqueIdentifierType & " = " & UniqueIdentifier & ";")
VBA Code:
Public Function ABC(UniqueIdentifierType, UniqueIdentifier, Output) As Variant
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=SQLOLEDB;Data Source=XXX\SQLEXPRESS;" & _
"Initial Catalog=MYDATABASE;" & _
"Integrated Security=SSPI;"
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString
Set rs = conn.Execute("SELECT " & Output & " FROM DATABASE WHERE " & UniqueIdentifierType & " = " & UniqueIdentifier & ";")
ABC = rs.Fields(0).Value
rs.Close
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Function