Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

"wrong Data Type Error" Public Lookup Function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • "wrong Data Type Error" Public Lookup Function

    Hello Gents,

    I have attached a file called Test2 which has a public function called DBLookup. The function looks in the attached access database simulating a Vlookup just like in Excel. The problem I am having is when that data appears on sheet1. I am getting #VALUE in the cell and the erroe states "a used value in the formula is of the wrong data type".

    my code:

    Code:
    Dim adoCN As ADODB.Connection
    Dim strSQL As String
    
    Const DatabasePath As String = "C:\Test2.mdb"
    
    'Function argument descriptions
    'LookupFieldName - the field you wish to search
    'LookupValue - the value in LookupFieldName you're searching for
    'ReturnField - the matching field containing the value you wish to return
    
    Public Function DBVLookUp(TableName As String, _
                              LookUpFieldName As String, _
                              LookupValue As String, _
                              ReturnField As String) As Variant
        Dim adoRS As ADODB.Recordset
        If adoCN Is Nothing Then SetUpConnection
    
        Set adoRS = New ADODB.Recordset
        strSQL = "SELECT * " & _
                 " FROM " & TableName & _
                 " WHERE " & LookUpFieldName & "=" & LookupValue & ";"
                 ' If lookup value is a number then remove the two '
        adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
        If adoRS.BOF And adoRS.EOF Then
            DBVLookUp = "Value not Found"
        Else
            DBVLookUp = adoRS.Fields(ReturnField).Value
        End If
        adoRS.Close
    End Function
    
    Sub SetUpConnection()
        On Error GoTo ErrHandler
        Set adoCN = New Connection
        adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
        adoCN.ConnectionString = DatabasePath
        adoCN.Open
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation, "An error occurred"
    End Sub
    Attached Files

  • #2
    Re: "wrong Data Type Error" Public Lookup Function

    Your textual lookup requires the value to be incased in single quotes.
        strSQL = "SELECT * " & _
    " FROM " & TableName & _
    " WHERE " & LookUpFieldName & "='" & LookupValue & "';"

    Cheers
    Andy

    Comment


    • #3
      Re: "wrong Data Type Error" Public Lookup Function

      Moving this to the SQL forum.
      .

      Comment


      • #4
        Re: "wrong Data Type Error" Public Lookup Function

        Andy,

        Thanks for the quick response. I tried the new SQL string and it's the same result.

        Any ideas?

        Comment


        • #5
          Re: "wrong Data Type Error" Public Lookup Function

          Any Pope's solution worked fine on my comp

          i've played around with it a little and get the same results...

          HTH

          Code:
          Public adoCN As ADODB.Connection
          Public adoRS As ADODB.Recordset
          Public strSQL As String
          
          Const DbPath As String = "C:\Test2.mdb"
          
          'Function argument descriptions
          'LookupFieldName - the field you wish to search
          'LookupValue - the value in LookupFieldName you're searching for
          'ReturnField - the matching field containing the value you wish to return
          
          Public Function DBVLookUp(TableName As String, _
                                    LookUpFieldName As String, _
                                    LookupValue As Range, _
                                    ReturnField As String) As Variant
                                    
              'dim adoRS As ADODB.Recordset
              If adoCN Is Nothing Then Call SetUpConnection
          
              Set adoRS = New ADODB.Recordset
              
                  strSQL = "SELECT * " & _
                           " FROM " & TableName & _
                           " WHERE " & LookUpFieldName & "='" & LookupValue & "';"
                           ' If lookup value is a number then remove the two ' <<-- that doesn't matter because your field dataType values are stored as text :) 
                       
                      adoRS.Open strSQL, adoCN, adOpenDynamic, adLockReadOnly
              
                          If adoRS.BOF And adoRS.EOF Then
                              DBVLookUp = "" ' Or A Comment
                          Else
                              DBVLookUp = adoRS.Fields(ReturnField).Value
                          End If
              
                  adoRS.Close
                  
              Set adoRS = Nothing
              Set adoCN = Nothing
              
          End Function
          
          Public Function SetUpConnection()
          
              On Error GoTo ErrHandler
                  
                  Set adoCN = New Connection
              
                  adoCN = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & DbPath & ";"
              
          '        adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
          '
          '        adoCN.ConnectionString = DbPath
                  adoCN.Open
                  
                  Exit Function
              
          ErrHandler:
                      MsgBox Err.Description, vbExclamation, "An error occurred"
                      
          End Function
          and enter the formula in B2 and "A" in A2 =DBVLookUp("account","account_id",A2,"account_description")

          Other than that i don't know what to suggest?

          z

          Comment


          • #6
            Re: &quot;wrong Data Type Error&quot; Public Lookup Function

            Hey Z,

            Not sure why...but when I deleted my old XL file and pasted the code into a new XL file it worked.

            Thanks for answering my post.

            X

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X