Results 1 to 4 of 4
  1. #1
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    51

    Dlookup with a function in the where statement

    I am having syntax errors using a dlookup with a function in the where section. I have a field with a 4 digit account number [CECODE]. I need to match it with a field that has only the first two digits followed by "xx"[Cost Account]. All of the fields involved are text fields.

    I have the following formula typed into the query: DLookUp("[Reporting Account]","2 Digit Reporting","[Cost Account]= " & Left([CECODE],2) & 'xx')


    I get the following error: Syntax error (Missing Operator) in query expression '[Cost Account]=63xx'

    I am a little confused because the error result looks exactly like I would expect. The text in [Cost Account] is 63xx so it seems like everything should be great. What is my missing operator?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    DLookUp("[Reporting Account]","2 Digit Reporting","[Cost Account]= '" & Left([CECODE],2) & "xx'")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    nigelbloomy is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2012
    Posts
    51
    It works perfectly. I hadn't thought of putting the xx between the double quote and single quote. The syntax for the dlookup can be confusing. Thank you for figuring it out for me.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. This may help you in the future:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replace DLookUp() with a SELECT statement?
    By Artist.Anon in forum SQL Server
    Replies: 9
    Last Post: 02-18-2013, 12:31 AM
  2. DLOOKUP function
    By tariq1 in forum Programming
    Replies: 5
    Last Post: 07-17-2012, 04:22 PM
  3. using the and function in a dlookup statement
    By englisap in forum Programming
    Replies: 10
    Last Post: 01-10-2011, 09:53 PM
  4. Help Using Variable in DLookup Statement
    By bcmarshall in forum Access
    Replies: 9
    Last Post: 12-02-2010, 12:44 AM
  5. Need Help for Dlookup function
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 01-04-2006, 08:18 AM

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