Results 1 to 9 of 9
  1. #1
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50

    Yet Another Dlookup isn't working thread

    hey everyone, well my titles not exactly right as I'm using Elookup but same syntax, potato-Potatoe



    heres my syntax

    Code:
    Me.Text33.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text32] And "Facility = " & [Forms]![Radio Switchboard]![Combo10])
    I'm trying to use 2 textboxes as criteria, the code works with the first one but I get a type-mismatch error when I try to bring in the 2nd criteria. The first field "Tested" is a date/time field but the textbox it uses is already formatted for dlookup (#1/1/2014# format). The 2nd field is just a text field that matches against another textfield. Where am I going wrong with my syntax? thanks!

  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
    You've left the word "And" out in the middle of nowhere. It needs to be inside the literal string, and concatenated.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Quote Originally Posted by pbaldy View Post
    you've left the word "and" out in the middle of nowhere. It needs to be inside the literal string, and concatenated.

    *this*-----^

  4. #4
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    I'm not very good at creating strings properly in vba, I went and tried to fix it and now I have a syntax error

    Code:
    Me.Text33.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text32] & " And Facility = " & [Forms]![Radio Switchboard]![Combo10])
    I feel like I'm extremely close, I'm guessing its a errant " missing. What am I missing?

  5. #5
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    Got it finally x.x, coding and little sleep is a bad combo
    Code:
    Me.Text33.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text32] & " And Facility = '" & [Forms]![Radio Switchboard]![Combo10] & "'")
    I didn't set the last field as string properly, Thanks for the help guys

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Offhand that looks appropriate for 2 numeric data types. Are they text?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    You have to include quotes around string values in criteria clauses. You can use single quotes, or use double quotes in a string to signify a a single double quote in the output, I mean like this: """cat""" --> "cat".
    Up above, this would mean "Tested = """ & [Forms and Stuff]![Text] & """ AND Facility..."

  8. #8
    drexasaurus's Avatar
    drexasaurus is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Location
    Santa Ana, CA
    Posts
    60
    I assumed text based on the name of the control? At least for the first one. Although I see he appropriately used single quotes in the second instance.

  9. #9
    g4tv4life is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    50
    The First text control is a date and the field type is date/time, the box preformats the # # for the dlookup as I wanted to remove another annoying variable as I was having trouble coding obviously and It was the easiest way to format it into my form. The 2nd control is a combo box with a string such as "John Muir Hospital", I should have used text deliminators sooner but least I finally got there in the end.

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

Similar Threads

  1. DLookup Code Not Working
    By burrina in forum Forms
    Replies: 7
    Last Post: 02-21-2013, 10:36 PM
  2. Autofill using DLookup not working
    By BBonexx in forum Access
    Replies: 2
    Last Post: 01-08-2013, 09:18 PM
  3. DLookup isn't working
    By cec in forum Access
    Replies: 7
    Last Post: 12-28-2012, 12:21 PM
  4. order by dlookup not working
    By broof in forum Queries
    Replies: 3
    Last Post: 01-11-2011, 03:15 PM
  5. DLookup code not working
    By lukekelly in forum Programming
    Replies: 9
    Last Post: 06-15-2010, 06:08 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