And your syntax is off?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.
In fact if I even try and add an = sign in front of the dlookup in design view it is removed.?
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
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 ".
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 =.
Last edited by June7; 01-07-2023 at 01:13 PM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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.)
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.