Any idea what I'm doing wrong with this LIKE criteria in DLOOKUP

[Welshgasman]

That is the title. The title doesn't have the example, and then there are these words: "Is there anything structurally wrong with this DLOOKUP..."
I still contend that the OP isn't doing anything "wrong". In fact, I use that exact syntax in a search form so the user can enter the wildcard if desired (the literal text is replaced with a variable).

Now, trying not to be lazy, but not knowing exactly where the OP is using the dlookup, I went one step further,
and found an additional question that has bothered me at times.
I copied (exactly) what works in the VBA immediate window to a query, and strangely, I get an error.
Maybe I'm doing the expression wrong. I would like to know how.



Attachment 49441

Attachment 49442

Adding an asterisk to the query makes no difference, still errors.

Again, the question isn't about usage or logic, it's about syntax.

And your syntax is off?
In fact if I even try and add an = sign in front of the dlookup in design view it is removed.?

[twgonder]

And your syntax is off?

Apparently the syntax is bad, but why, with or without = or """" instead of '", I have no idea.
I tried the expression builder, but I have no idea how it works with DLookup.

[twgonder]

The immediate window does not like the use of single quotes as a delimiter :

? "Fred " & 'Ginger'

Will result in an error
.

Oddly, ?'Ginger' doesn't create an error, you just don't get a result other than maybe null or ""
Or it thinks it's a comment with nothing to do.
In any case, it was bad design to use a single quote for comments. My old BASIC used ! for comments and that freed up the ' to use with complex strings needing both ' and ".

[June7]

Yes, that is odd because the concatenation definitely has error.

However, if you use all apostrophes then no error, just nothing - no null, not empty string.

But the expression is in a textbox so the apostrophes should be fine. So something else is issue.

And, as I said, the concatenation is unnecessary for the example given, whether using LIKE or =.

[twgonder]

Yes, that is odd because the concatenation definitely has error.
...
However, as I said, the concatenation is unnecessary for the example given, whether using LIKE or =.

Not odd if the immediate window treats the ' as a comment.
We can't be sure of that because we see differences between module code and what the immediate window does.

As to the unnecessary concatenation goes, that wasn't part of the original question, and you have to presume, as is often requested here, that the example was dummied down for clarification of what was the actual question.

I seem to catch hell here in this forum for answering a question/comment that wasn't the part of the original question in my own post. As if it was I that caused the scope creep. (Although, I often ask "how do you do that?" when someone makes a suggestion, or I say that doesn't work because of this other condition that wasn't included when I was trying to keep things simple in the example.)

[twgonder]

Post #13 Now, trying not to be lazy, ... Again, the question isn't about usage or logic, it's about syntax.

If this wasn't so sad, it would be funny.
I stumbled across the cause and NOT a solution to this problem.
I have regional settings for Colombia. As such "," and "." are often swapped.
It seems that queries can't handle a "," in a function like DLookup, but changing to a "." won't work either.
What's a developer to do other than to always use a USA region (which screws up other Office applications)?

Note: With a bit more testing I found one has to use a ";". As I said, it's so funny, I couldn't make this stuff up if I wanted to.