Can not get a match on a string

[CD Tom]

This is driving me crazy, I have an excel file that I load into a table. In this table there is a description of a category EX: "B Western" I have a table in my data base (Category) that has the description with B Western in it and also a class BW. I want to find this class so I do a dlookup to find the class, it looks like the description matches but it will not give me the class it comes up with a null

        VCat = rsimport("Description")
        VCategory = DLookup("[Class]", "[Category]", "[Description] = '" & VCat & "'")
 

is there something when I load the excel file that I don't see that could be causing this not to be found?
I've tried everything even using a Like instead of a = and have put * after the B Western but still get a null if I put a * in from and behind I do get a class code but because there is a B Western with the work Ladies in front it picks that one up. So it looks like there might be something in front of the B Western that I don't see. The excel field is formatted as a text field and when I changed it to general I get the same thing. I know that excel has a code that is put in from of the field for justification of the field and if that's the case how do I do away with that code.
Again any help is greatly appreciated.

[PhilOfWalton]

Try

 VCategory = DLookup("Class", "Category", "Description = '" & VCat & "'")
 

Your problem is for example that your original "[Category]" is looking for a table named [Category] including the square brackets.

Your table name is simply Category.

Phil

[CD Tom]

That's not the answer I use brackets in all my Dlookup and have no problem, I did try it without the brackets and get the same answer.

[ADezii]

Try Cleaning (removing all non-printable characters) the Excel Data prior to referencing it, as in:

Dim strTest As String
 
strTest = vbTab & "Help " & vbCrLf & "me!" & vbCrLf
 
Debug.Print "Before Clean: " & strTest
Debug.Print "After Clean : " & Application.WorksheetFunction.Clean(strTest)

Before Clean:   Help 
me!
 
After Clean : Help me!
 

[PhilOfWalton]

I apologise, I didn't know you could use square brackets.

This function is essentially the same as DLookup, but will allow you to step through the code and see what is happening.
Just change DLookup to ELookup

I suspect that your VCat is dodgy for some reason.

Feed in your in

Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional ORDERClause As Variant) As Variant
 
    On Error GoTo Err_ELookup
    'Purpose:   Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. [e-mail]
    'Updated:   December 2006, to handle multi-value fields (Access 2007.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim MyDb As DAO.Database          'This database.
    Dim Rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSql As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.
 
    'Initialize to null.
    varResult = Null
 
    'Build the SQL string.
    strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSql = strSql & " WHERE " & Criteria
    End If
    If Not IsMissing(ORDERClause) Then
        strSql = strSql & " ORDER BY " & ORDERClause
    End If
    strSql = strSql & ";"
 
 
    Set MyDb = CurrentDb
 
    Set Rs = MyDb.OpenRecordset(strSql, dbOpenForwardOnly)
    If Rs.RecordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(Rs(0)) = vbObject Then
            Set rsMVF = Rs(0).Value
            Do While Not rsMVF.EOF
                If Rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!FileName & strcSep
                Else
                    strOut = strOut & rsMVF![Value].Value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = Left(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = Rs(0)
        End If
    End If
    Rs.Close
 
    'Assign the return value.
    ELookup = varResult
 
Exit_ELookup:
    Set Rs = Nothing
    Set MyDb = Nothing
    Exit Function
 
Err_ELookup:
    MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
 
End Function
 

Phil