UDF to Pull SQL Data (non-numeric character issue)

jjobrien03

New Member
Joined
Sep 2, 2014
Messages
39
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 & ";")


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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jjobrien03

New Member
Joined
Sep 2, 2014
Messages
39
Example data types below
9334417621 (works)
Excel Apartments (does not work)
ABCFR15XY10-90.000 (does not work)
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Text values in SQL need proper kind of quotations marks around them. You do not insert them.

J.Ty.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,218
Messages
5,623,461
Members
415,971
Latest member
Graeme M

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top