Data type mismatch help MS Access VBA

Question

I keep getting a Data type mismatch when trying to run a lookup. I have a table tWorkOrder and has a Short Text column salesOrderNo Number column workOrderNo and Short Text lineKey I am searching for the lineKey value in my lookup.

    Dim lineKeyOW, SoNo, WTSo As String
    SoNo = 0135487
    WTSo = 2
    lineKeyOW = Nz(DLookup("lineKey", "tWorkOrder", "salesOrderNo = '" & soNo & "' AND workOrderNo = '" & WTSo & "'"), "NA")

Thomas G · Accepted Answer · 2016-04-22 15:39:27Z

2

Like this will be better

Dim lineKeyOW As String
Dim SoNo as String
Dim WTSo As Long

SoNo = 0135487
WTSo = 2
lineKeyOW = Nz(DLookup("lineKey", "tWorkOrder", "salesOrderNo = '" & soNo & "' AND workOrderNo = " & WTSo ), "NA")

First, workOrderNo column being Number datatype, you should not surround it's value with '

Then, never do this :

Dim lineKeyOW, SoNo, WTSo As String

Because it is equivalent to this

Dim lineKeyOW As Variant, SoNo As Variant, WTSo  As String

Which is plain wrong for several reasons.

If you really want an inline dim declaration, you should do

Dim lineKeyOW As String, SoNo As String, WTSo  As String

edited Apr 22, 2016 at 15:39

answered Apr 22, 2016 at 14:52

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin Over a year ago

Thank you! I had a feeling it had something to do with the " and ' I always have had a hard time with them.

Thomas G Over a year ago

@Kevin That will come with practicing.

Kevin Over a year ago

Also thank you for the explanation on declaring variables that was very helpful.

Collectives™ on Stack Overflow

Data type mismatch help MS Access VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related