Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11

    Elookup how to format or translate a dlookup to get same answer

    I POSTED A SIMILAR QUESTION WITH MREXCEL (JUST JOINED ALSO)
    I WOULD LIKE TO LEARN HOW TO USE BOTH ACCESS AND EXCEL FOR THE SAME END PURPOSE
    I DISCOVERED ELOOKUP AND THOUGHT IT WAS BETTER BECAUSE OF THE ERROR MSG
    I FORMATED THE ELOOKUP THE SAME AS DLOOKUP BUT IT SEEMS TO BE DIFFERENT
    I HAVE A 2 FIELD TABLE A STRING & B A # AND I AM LOOKING UP THE #
    ANSWER= DLOOKUP(# "TABLE",FIELD) PROBLEM SOMETIMES DOES NOT WORK BUT NO ERROR FLAGGED
    TRIED THE SAME WITH ELOOKUP
    ANSWER= ELOOKUP(# "TABLE",FIELD)
    I GET A ByREF ERROR SO I THINK I FORMAT ELOOKUP WRONG
    I AM THANKFUL FOR ALL THESE GREAT FORMS OF PEOPLE TRYING TO HELP EACH OTHER


    THERE IS STILL HOPE FOR THE WORLD TO SURVIVE MY GENERATION MADE A MESS OF

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Please don’t use capitals - it is akin to shouting and more difficult to read. To answer you question you need to provide the vba code to the elookup function

  3. #3
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    My apologies at 80 my eyes are not as good as when I was 30
    If I could translate DLookup to ELookup I could show you
    I think it involves a query not sure
    If so the queryX would be a table name and the answer a number
    TblL = "Msdh"
    RkT = "ML"
    RL = DLookup("PCT", TblL, [RkT] = "RkT")
    I hope the larger font helps you

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Elookup is not a regular vba function, it will be one created by someone in vba and you would need to copy and paste it into a module in your application.

  5. #5
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _

    'Build the SQL string.
    strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
    strSql = strSql & " WHERE " & Criteria
    End If
    Is this where I start?
    SELECT ("CsDH.*FROM CsDH")

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    ELookup is a function created by Allen Browne. More details here http://allenbrowne.com/ser-42.html
    I've never used it so can't advise.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    from Brown's site
    How does it work?​


    The function accepts exactly the same arguments as DLookup(), with an optional fourth argument. It builds a query string:
    SELECT Expr FROM Domain WHERE Criteria ORDER BY OrderClause
    RL = DLookup("PCT", TblL, [RkT] = "RkT")

    do I replace Expr with PCT
    lost from there...... table is what Domain?
    do I need criteria....is it RkT
    RL = ELookup("PCT", "CsDl" = "ml")
    closer says "can't find table or query" CsDL is the table
    what is the query



  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you are not telling us what we need to know

    I doubt this works

    RL = DLookup("PCT", TblL, [RkT] = "RkT")

    but this might

    RL = DLookup("PCT", TblL, "[RkT] = " & RkT)

    or perhaps

    RL = DLookup("PCT", "TblL", "[RkT] = '" & RkT & "'")



    but without seeing the code for Elookup, impossible to say

  9. #9
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    tried....
    TblL = "CsDl" ' tried with and without ""
    'I wonder why I can"t assign TblL
    ML = 1
    'can't find table here
    RL = ELookup("PCT", "TbLL", "ml =" & [ML])
    'too few parameters expect 1....here
    RL = ELookup("PCT", "CsDl", "ml =" & [ML])

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    At the moment the message we are getting is like you ringing up your garage and saying 'my car won't start, I've tried to change the insurance, and I've got a blue thingy, please help'

    suggest you tell us in simple terms (no code) what you are trying to achieve.

  11. #11
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    table 2 fields
    RkT PCT
    ml 90
    looking for 90 from elookup

  12. #12
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    sorry
    2 fields from many tables the reason for the TblL=a name of table
    RkT PCT many names with different numbers
    ml 90
    looking for 90 from elookup
    I have no idea why dlookup stopped working in my sub proc so the reason for elookup

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I set up a dB for testing using ELookup() .
    You will need to add more values or import more tables - you didn't provide a very big sample set....
    Attached Files Attached Files
    Last edited by orange; 02-18-2019 at 06:06 PM.

  14. #14
    REALYOLDMAN is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    CA
    Posts
    11
    sub C()
    If Tdis < 8 Then
    Select Case Tsuf
    Case "T"
    TblH = "CsTh"
    TblL = "CsTL"
    Case "D"
    TblH = "CsDh"
    TblL = "CsDL" ' C is sub s is Tdis D is the D L or H value types ... the reason for assignment
    End Select
    call sub 2
    end sub


    sub 2()
    If MLrk = IsNumeric(MLrk) Then
    ElseIf MLrk < 3 Then
    RkT = "ML" '2
    ML = DLookup("PCT", TblL, "[RkT] = " & RkT)
    end if
    end sub
    does more from here


    how do I make this work with the above
    this looks like the translation I asked for
    some kind of sub that gets the above info
    Private Sub btnELookupTest_Click()
    Dim tmp As String '<<-- holds a value for the field RKT
    Dim MyDomain As String '<<-- holds a value for the table/query name


    MyDomain = Me.MyTable '<<-- table name from form
    tmp = Me.RKTValue '<<-- tRKT Value from form


    Me.theResult = ELookup("PCT", MyDomain, "RKT = '" & tmp & "'")


    End Sub

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if RKT is the table is a number datatype then you use

    Me.theResult = ELookup("PCT", MyDomain, "RKT = " & tmp)

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

Similar Threads

  1. Replies: 3
    Last Post: 12-08-2017, 03:49 PM
  2. Replies: 4
    Last Post: 06-26-2017, 12:19 PM
  3. Replies: 12
    Last Post: 06-13-2014, 01:02 AM
  4. Error using ELookup but not DLookup
    By Rawb in forum Programming
    Replies: 12
    Last Post: 10-18-2010, 07:09 AM
  5. Replies: 3
    Last Post: 10-06-2009, 02:11 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