Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

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


    Is there anything structurally wrong with this DLOOKUP in a report field's control source? Can't figure out why I'm getting an #Error message.

    Code:
    =DLookUp("[Perc]","qryPercentages","[employeename] LIKE '" & 'SMITH' & "'")

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by orange View Post
    What is the error message?
    Sorry...the dlookup is in the control source of a field in a report. When I run the report, its shows '#ERROR'

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What you have has unnecessary concatenation but shouldn't error. LIKE is customarily used with wildcard.

    =DLookUp("[Perc]","qryPercentages","[employeename] LIKE '*SMITH*'")

    If you want to find a specific record, really should do search on employee ID, not name, and use =.

    Why have a static parameter? What if report is for JONES?

    What do you want to happen if there are multiple SMITH's?

    What data type is employeename? Is it a text field holding name or is it a number type lookup and actually contains employeeID?
    Last edited by June7; 01-06-2023 at 02:41 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.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Multiple single quotes?
    Put the criteria into a string variable, then you can debug.print that until you get it correct, then you can use that in the function. If you still cannot see the error, you can post the output of the debug.print here or elsewhere you have asked.

    Even test it in the imemdiate window first, as this gives an error?

    tt="[employeename] LIKE '" & 'SMITH' & "'"
    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

  6. #6
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Must use double quote for the SMITH

    Maybe this little excursion into the immediate window of VBA will better explain things?
    It's from a .accdb I had handy from another thread, but you should get the idea, same syntax.

    The last line caused the compile error.

    Short answer, put SMITH in double quotes, not single.

    Click image for larger version. 

Name:	220106dlookup.jpg 
Views:	17 
Size:	47.7 KB 
ID:	49438

    This is a similar problem to what I commonly experience, in that the old BASIC I used allowed single and double quotes for literal strings. Not so in VBA.
    SQL in Access, on the other hand, does allow single quotes in its syntax. Just to confuse us I suppose.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Like does not work without an asterisk?
    Try it out for yourself.
    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

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Typical usage Dlookup with Like and wildcard *

    ?dlookup("animalid","animal","Aname like '*mq*'")
    16
    ?dlookup("animalid","animal","Aname like '" & "*mq*" & "'")
    16

    See Allen Browne
    Quotes within quotes
    and Dlookup

    Microsoft Quotation marks in string expressions

    Attachment 49439

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Without * is okey

    Quote Originally Posted by Welshgasman View Post
    Like does not work without an asterisk?
    Try it out for yourself.
    Look at post #6. LIKE works just fine without an asterisk.
    Here's another example:
    Click image for larger version. 

Name:	220106dlookup2.jpg 
Views:	17 
Size:	29.3 KB 
ID:	49440

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That is because it happens to be EQUAL to what you are looking for?

    If the entry was " Look at Mc51 after entry" it would not be found.

    The keyword LIKE is a clue (well to some people at least).
    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

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Welshgasman View Post
    That is because it happens to be EQUAL to what you are looking for? ...
    This thread is a classic example of some of the experiences I have had in asking questions here in this forum.
    In the same breath, I can say, it's a classic problem I had over the years with the many programmers I had hired.
    I'm not a programmer by education, rather an accountant and lawyer, where language and comprehension are very important.

    The OP asked a very simple question, and gave a very simple explanation with an example that should have tipped off anyone that (s)he was working with a report based on a query, not VBA code (even more so given the forum it was asked in).

    The OP didn't ask how to use dLookup with wildcards. Giving the benefit of the doubt, I assumed that the OP knew what (s)he was doing, maybe even in some cases wanting to get the exact record instead of a selection of records without having to change the like to an "=" (although a dLookup IN THIS QUESTION is only going to return the first valid field that is encountered). I can properly assume all this this since the question was about syntax and not usage.

    In post #6 I gave a sufficient explanation that exposed the incorrect information in some other replies. And yet, even after post #6, the erroneous replies continue.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The OP didn't ask how to use dLookup with wildcards. Giving the benefit of the doubt, I assumed that the OP knew what (s)he was doing....
    But the OP titled the question
    "Any idea what I'm doing wrong with this LIKE criteria in DLOOKUP "


    So there is a difference in understanding the post. I didn't assume the OP knew what he/she was doing. I interpreted the post as "I'm not sure what I'm doing with DLookup criteria with LIKE operator".

    Let's wait for the OP to respond and/or provide more contextual info.

    Interesting how you see responses, other than yours, as erroneous. And they may prove to be so, once we get a response from the OP.

    This thread, like so many, lacks some context and, as you have highlighted, leads to a variety of interpretations and responses. Some people are better guessers than others. Hopefully, we all learn something from these threads - whether it's technical, syntax, a tip or just how to ask a better question.

  13. #13
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    An ancillary question/observation

    Quote Originally Posted by orange View Post
    But the OP titled the question
    "Any idea what I'm doing wrong with this LIKE criteria in DLOOKUP " ...
    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.

    Click image for larger version. 

Name:	220106dlookup3.jpg 
Views:	16 
Size:	133.5 KB 
ID:	49441

    Click image for larger version. 

Name:	220106dlookup4.jpg 
Views:	16 
Size:	16.2 KB 
ID:	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.
    Last edited by twgonder; 01-06-2023 at 09:17 AM. Reason: add example

  14. #14
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by orange View Post
    But the OP titled the question
    ...
    Interesting how you see responses, other than yours, as erroneous. And they may prove to be so, once we get a response from the OP....
    No need to wait for the OP, READ the responses in the posts (I decline to point to exactly any persons that have been helpful to me in the past) and then look at the examples in post #6 & 9. Lots of (yes, erroneous) answering before understanding or testing.

  15. #15
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The immediate window does not like the use of single quotes as a delimiter :

    ? "Fred " & 'Ginger'

    Will result in an error

    ? "Fred " & "Ginger"

    Does not.

    Single quotes or a concatenated double quote (or triple quotes) are perfectly acceptable and even required in VBA.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Value from DSUM with multiple criteria wrong
    By maxmaggot in forum Reports
    Replies: 4
    Last Post: 04-12-2014, 05:21 PM
  2. What's wrong with this Criteria
    By djclntn in forum Queries
    Replies: 3
    Last Post: 10-28-2013, 01:23 PM
  3. What am i doing wrong? Dlookup
    By RockOnRay in forum Access
    Replies: 2
    Last Post: 05-01-2013, 01:40 PM
  4. Replies: 1
    Last Post: 08-03-2012, 12:32 PM
  5. What is wrong with my DLookup?
    By BRV in forum Programming
    Replies: 8
    Last Post: 11-03-2011, 01:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums