DLookup multiple criteria

[mikmonto]

Hi,

I have an issue with a DLookup:

WEAVEPR = DLookup("Speed", "WIP_GANTT_WEAVE_PR", ("Type =" & "Forms![WIP_GANTT]!LoomType") And ("[Location] =" & "Forms![WIP_GANTT]!Country"))

Both LoomType and Country are Text

If I use just one of the two conditions, the DLookup works, otherwise it does not give me the expected output.

Any hints on this?

Thanks,
M

 

[plog]

A bunch of things, mostly due to your improper use of quote marks. Double quotes need to be around string literals (text that is not coming from a variable/outside source, aka hard coded). When doing comparisons of strings you need to surround those by single quotes within the double quoated text. You also need to seperate the string literals from the variables with an ampersand (&). And for good measure put brackets around string literals that are variable names. This will be your criteria argument of the Dlookup:


"[Type] ='" & Forms![WIP_GANTT]!LoomType & "'' And [Location] ='" & Forms![WIP_GANTT]!Country & "'"

 

[mikmonto]

Thanks Plog,

Just tested it but actually not working yet

WEAVEPR = DAvg("Speed", "WIP_GANTT_WEAVE_PR", "[Type] ='" & Forms![WIP_GANTT]!LoomType & "'' And [Location] ='" & Forms![WIP_GANTT]!Country & "'")

(Just changed DLookup with DAvg, but I had the same error with DLookup)

Thanks,
M

 

[plog]

There was an extra single quote before the AND:

WEAVEPR = DAvg("Speed", "WIP_GANTT_WEAVE_PR", "[Type] ='" & Forms![WIP_GANTT]!LoomType & "' And [Location] ='" & Forms![WIP_GANTT]!Country & "'")

 

[mikmonto]

Hero! It worked perfectly.

Thanks,
M