CODEWITHSUNDEEP
ms-accessvba
Shubham
Shubham

Reputation: 979

Access VBA: DLookUp function giving type mismatch error

I'm trying to run the following code, but I'm getting a "Type mismatch" compile error on DLookUp.

DLookUp returns a value, right? This code, to me, says: in the strSQL recordset, look for where the SKUS_ORDERED column equals curSKU2. strSQL, temp, curSKU2 are all initialized as string type variables.

...
 strSQL = "SELECT * FROM ORDER_DATA WHERE [ORDER]=" & curOrder

 Dim temp As String
 temp = DLookup("SKUS_ORDERED", db.OpenRecordset(strSQL), SKUS_ORDERED = curSKU2)
...

Where is the type mismatch? Can anyone help me out?

EDIT:

       ...
        Set fld_orders = rst_orders.Fields("ORDER")
        curOrder = fld_orders.Value

        Dim temp As String
        temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder)

        If temp <> Null Then MsgBox temp
       ...

The entire code is pretty long but here's a larger snippet of where curOrder is initialized, this is inside a nested loop, curSKU2 is initialized earlier outside the loop. Hope it helps.

Upvotes: 3

Views: 10184

Answers (2)

HansUp
HansUp

Reputation: 97101

I suggest you change this section of your code ...

Dim temp As String
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder)

to this ...

Dim temp As String
Dim strCriteria As String
strCriteria = "SKUS_ORDERED = '" & curSKU2 & "' AND [ORDER] = " & curOrder
Debug.Print strCriteria
Debug.Print TypeName(DLookup("SKUS_ORDERED", "ORDER_DATA", strCriteria))
temp = DLookup("SKUS_ORDERED", "ORDER_DATA", strCriteria)

If you get an error, switch to the Immediate Window to view the output from the Debug.Print statements.

The first will give you the text for a WHERE condition which you can test in a new query:

SELECT SKUS_ORDERED FROM ORDER_DATA WHERE [strCriteria text here]

The TypeName() function will tell you the data type of the value returned by DLookup(). If TypeName says Null, you will get an error when you try to assign it to a string variable (temp), because a string value can never be Null.

Upvotes: 2

Christian Specht
Christian Specht

Reputation: 36421

The mismatch occurs because the second parameter needs to be a string, not a RecordSet.
If any of the parameters in the third argument is a variable (like in your case), the third argument needs to be a concatenated string as well:

temp = DLookup("SKUS_ORDERED", "ORDER_DATA", _
            "SKUS_ORDERED = '" & curSKU2 & "' and ORDER = " & curOrder)

EDIT:

Without more code, it's difficult to see where you are using Null.
Are the table name and the column names correct?
What types are your variables? Do they really have values?
Can you post some more code where we can see how you declare and fill the variables?

The "_" character indicates a line break. I could have written the whole statement in one line, but then you'd have to scroll to see it completely:

temp = DLookup("SKUS_ORDERED", "ORDER_DATA", "SKUS_ORDERED = '" & curSKU2 & "' and ORDER = " & curOrder)

EDIT 2:

Could you show the parts where both variables are declared and where curSKU2 is initialized as well? With what you posted, one still can't see if curSKU2 is even filled and what types both are.

Plus, temp is declared as string, so it can never be Null.
This has two consequences:

  1. If temp <> Null doesn't make sense.
  2. DLookup returns Null when no record is found, so the type mismatch could be in the line temp = DLookup(...).
    Try temp = Nz(DLookup(...)) instead.

Upvotes: 5

Related Questions