Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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:

    VB:
     
    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.
    VB:
    strSQL = "SELECT * " & _ 
    " FROM " & TableName & _ 
    " WHERE " & LookUpFieldName & "='" & LookupValue & "';" 
    
    

    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

    VB:
     
    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 ' [B]<<-- that doesn't matter because your field dataType values are stored as text :) [/B]
         
        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, 01:35
  2. "Type Mismatch" error
    By RichardHarpham in forum EXCEL HELP
    Replies: 1
    Last Post: August 4th, 2006, 18: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, 00:15
  4. Replies: 2
    Last Post: September 17th, 2005, 07:20
  5. Replies: 1
    Last Post: November 18th, 2004, 23: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