Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Excel VLookup equivalent in Access

    Hi,
    Does Access have an equivalent function Excel's VLookup?

    If so it may answer a query I posted earlier

    Regards
    John

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    what VLookup does? there is a function called DLookup() but I'm not sure if it is what you are looking for.
    ghozy.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    With Vlookup in Excel you can get the function to find a value from a range of values, eg
    From To age
    0.457 0.55 500
    0.56 3.25 400
    3.26 3.93 300
    3.94 4.75 250

    If I'm looking for an items age that has a value of 3.66 then the function will return 300 because age falls between the category of 3.26 to 3.93

    Regards
    John

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    yes dlookup what you are looking for. with dlookup you can find a value in a table based on a criteria.
    Usage: Dlookup("LookupField", "YourTable", "Criteria")
    The Function below finds age from YourTable with AValue given to function.

    Code:
    Public Function FindAge(AValue as Double)
    	FindAge = DLookup("age", "YourTable", CStr(Avalue) & " Between [From] AND [To]"
    End Function
    You can find more info in Access Help.
    Last edited by ghozy; 06-09-04 at 16:16.
    ghozy.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Here as an example I use in an Expression on a SetValue macro for doing insurance premiums

    =DLookUp("[VTermNS]","1New1VTermM","[AgeNB]=[Forms]![SumPremAllCats1]![anb]")

    Where VTermNS is a field in a table with an insurance rate, 1NewVTermM is the table and AgeNB is a field in the same table with age next birthdays.

    [anb] is the age next birthday on the form SumPremAllCats1

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Guys,
    Thanks for the replies.

    It looks like Dlookup will do what I need to to do, I've tried it in a form and it gives the values that I need correctly. So now its how I implement it.

    The full syntax in my example is
    =DLookUp("[Ageing]![Details]","ageing"," [Collection_notes2]![Days age] between [Ageing]![Age_lower_level] and [Ageing]![Age_upper_level] ")

    Ideally (although against database design advice) I need this value in a field as it is used so much elsewhere in the database. The best way for me was if I could have the flexibility of Dlookup but implement it in a query that originally imports the data, How can I achieve this?

    Regards
    John

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    this SQL line should do same job. it only returns 1 result back. if you want to see all records fits into the criteria you should remove TOP 1 part from SQL code.

    Code:
     
    SELECT TOP 1 [Ageing]![Details] 
    FROM ageing 
    WHERE [Collection_notes2]![Days age] BETWEEN [Ageing]![Age_lower_level] AND [Ageing]![Age_upper_level]
    Last edited by ghozy; 06-10-04 at 11:53. Reason: typo in the SQL code
    ghozy.

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Ghozy,
    I've slightly amended you suggestion and it works perfectly, thanks very much mate, its much appreciated

    Regards
    John

  9. #9
    Join Date
    Feb 2013
    Posts
    1

    Re: equivalent of xl Lookup in access

    Hi All,

    Many thanks for your post relating to above. I am trying to achieve the same objective in a reporting database am trying to build. but am a bit stuck. My issue is around two tables

    1. PAYGAgreement

    2. qryLookupdata

    I am tasked to create a calculated field named NewAgreement2 based on values in Newagreement field looking into PAYGAgreement table and returning the respective percentage.

    Attached are extract copies of the two tables.

    I have tried several methods. None seems working at the moment. I will appreciate if anyone can assist by pointing in the right direction.

    Many thanks.



    Afolabi
    Attached Thumbnails Attached Thumbnails PAYGAgreement.bmp   qryLookupdata.bmp  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •