DLookup Multi-criteria

Thales750 · May 21, 2018

Can someone tell me what I am missing here please?

Why does this work?

Code:

pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", "swrComplete = False")
pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", "swrCanceled = False")

and this doesn't?

Code:

        pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", "swrComplete = False" And "swrCanceled = False")

I get a "Type Mismatch" error.

Thanks, I always have trouble with multiple criteria.

MarkK · May 21, 2018

This is a problem with strings, not with multiple criteria. The expression should be ...

Code:

"swrComplete = False And swrCanceled = False"

...but when you do...

Code:

"swrComplete = False" And "swrCanceled = False"

...you are attempting to And two strings together, but And is not a valid operation on strings, thus, you get a type mismatch.
hth
Mark

Pat Hartman · May 21, 2018

The two examples don't do the same thing.

Your compound condition doesn't work because it is not formatted correctly. The quotes are misplaced.

Code:

pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", "swrComplete = False And swrCanceled = False")

Better because it is easier to debug -

Code:

Dim strCriteria
strCriteria = "swrComplete = False And swrCanceled = False"
pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", strCriteria)

You can always print strCriteria to the debug window so you can see what your string actually looks like. You would see that yours looks like

"swrComplete = False"

and the rest is confusing the compiler since it doesn't expect anything to follow the criteria expression.

Thales750 · May 21, 2018

Pat Hartman said:
The two examples don't do the same thing.

Your compound condition doesn't work because it is not formatted correctly. The quotes are misplaced.

Code:

pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", "swrComplete = False And swrCanceled = False")

Better because it is easier to debug -

Code:

Dim strCriteria strCriteria = "swrComplete = False And swrCanceled = False" pubSwitchRequestID = DLookup("swrSwitchRequestID", "tblSwitchReport", strCriteria)

You can always print strCriteria to the debug window so you can see what your string actually looks like. You would see that yours looks like

"swrComplete = False"

and the rest is confusing the compiler since it doesn't expect anything to follow the criteria expression.

That is way better.

This is a serious Eureka moment. Thank you.

DLookup Multi-criteria

Thales750

Formerly Jsanders

MarkK

bit cruncher

Pat Hartman

Super Moderator

Thales750

Formerly Jsanders

Similar threads

Users who are viewing this thread