Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: "wrong Data Type Error" Public Lookup Function

  1. #1
    Join Date
    18th July 2006
    Posts
    67

    "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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: "wrong Data Type Error" Public Lookup Function

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

    Cheers
    Andy


  3. #3
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: "wrong Data Type Error" Public Lookup Function

    Moving this to the SQL forum.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th July 2006
    Posts
    67

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th July 2006
    Posts
    67

    Re: "wrong Data Type Error" 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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. "Matrix" lookup User Defined Function
    By frankball in forum Non Confidential Showcase
    Replies: 8
    Last Post: August 26th, 2006, 02:35
  2. "Type Mismatch" error
    By RichardHarpham in forum EXCEL HELP
    Replies: 1
    Last Post: August 4th, 2006, 19:46
  3. "Compile error: user-defined type not defined" in Excel VB6 Macro
    By desiboy in forum Excel and/or Access Help
    Replies: 3
    Last Post: December 28th, 2005, 01:15
  4. Replies: 2
    Last Post: September 17th, 2005, 08:20
  5. Replies: 1
    Last Post: November 19th, 2004, 00:22

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno