Defining a variable with multiple criteria

Question

I am attempting to lookup a variable that matches the two given criteria. Please see script below:

Dim usr As String
Dim pafa As String
usr = DLookup("UserID", "tt_CurrentUser")
pafa = DLookup("PassFail", "dbo_TestDetail", [WHERE "dbo_TestDetail.TestIndex" = " & Me!JobLU & " And "dbo_TestDetail.SN" = " & Me!SN & "])

As shown, pafa is the variable I am having issues with.

Run-time error '2465' pops up with a message reading "Manufacturing Test Manager can't find the field '|1' referred to in your expression".

When I hit Debug, line 4 is highlighted. " & Me!JobLU & " and " & Me!JobLU & " are both the correct values when I hover my mouse over the text.

What am I doing wrong when defining these two criteria?

Also, pafa is going to equal either "Fail" or Null.

Why have you tagged SQL server and not the language your code is written in? — Tab Alleman, Dec 10 '18 at 16:00
You should check DLookup sintaxis https://support.office.com/en-us/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937 — Juan Carlos Oropeza, Dec 10 '18 at 16:08

Dávid Laczkó · Answer 1 · 2018-12-10T16:32:01.390

0

Why do you use WHERE and the brackets? The quotations are also wrong. When doing concatenation you need to distinguish literal string and identifiers that need to be evaluated. Try

"dbo_TestDetail.TestIndex = " & Me!JobLU & " And dbo_TestDetail.SN = " & Me!SN

Hovering the mouse over expressions and see the correct evaluation doesn't mean that an outer expression is written correctly.

edited Dec 10 '18 at 16:32

answered Dec 10 '18 at 16:26

Dávid Laczkó

1,091
2
6
25

It's giving a run-time error '94': Invalid use of Null – Adam S. Dec 10 '18 at 16:33
See [this](https://stackoverflow.com/questions/5630961/null-values-for-variables-in-vba) for a possible change of your data type. – Dávid Laczkó Dec 10 '18 at 16:45

Defining a variable with multiple criteria

1 Answers1