Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12

    Exclamation Unanswered: Vlookup for access

    I've been trying to use in access the vlookup function. I've tried the dlookup but it just isn't the same.
    I've got two files: one with the invoices of my firm and the other one with the quotes for the products in the invoices. These quotes have a start and end date as well as the specific code of the product. The file with the invoices only have the product code and the date it was bought.
    Why won't dlookup do? because there will never be exact matches to look for but specific intervals. The last option on the vlookup function in excel is the key here; that's what i need in the dlookup but there is none.
    I've tried to create a substitute for vlookup in excel and then export it to access. Access again was limited to a few formulas and did not have index or match (obviously because in access there is no specific location such as: cell E537).

  2. #2
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Let me get this straight:

    File 1;
    Product Code
    Start Date
    End Date

    File 2:
    Product Code
    Day Bought


    What exactly do you want out of these two files (are they tables?)
    please state your aim

  3. #3
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    You got it right, what I want is to search in the first file the correct quote for the products in file 2, therefore verifying if I paid the correct price.
    This is to make sure there is no price difference. I did it in excel, but can't make it work in access.

    Originally posted by Jerrie
    Let me get this straight:

    File 1;
    Product Code
    Start Date
    End Date

    File 2:
    Product Code
    Day Bought


    What exactly do you want out of these two files (are they tables?)
    please state your aim

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    does what you want to do make use of the fields i mentioned in my first post? is this what you want?

    check to see if day bought in file #2 for that specific product code is between start date and end date in file #1?

    where is this money thing from?

    sry bout the questions

  5. #5
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    In excel i used concatenate to turn the product code and the date into a single new code. I also used concatenate to merge the product code, the start date and the end date into a single code in the first file.
    I used vlookup to make a specific cell return the correct value for that product at the given time.
    This is just a mechanism i created to compare things in my company.

    Any suggestions?



    Originally posted by Jerrie
    does what you want to do make use of the fields i mentioned in my first post? is this what you want?

    check to see if day bought in file #2 for that specific product code is between start date and end date in file #1?

    where is this money thing from?

    sry bout the questions

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by marsoncunha

    Any suggestions?

    Normalize your data.



    You can't do what you want to do with data that has no structure.
    Last edited by Teddy; 03-12-04 at 16:13.

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lol why concatanate? i still dont get the aim here; note that nothing is impossible in access since access and excel are like sisters;

    "I used vlookup to make a specific cell return the correct value for that product at the given time." <-- is this the aim?

    if so, whats the correct value? whats that value?

    either way, you can conatenate stuff in access; just do [CurrentDate] & [ProductCode]

  8. #8
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    Because in excel vlookup only accepts one specific vairable to look for and since one file has two variables and another has 3, it's gonna be hard to work with this if not with concatenate...
    i know there is concatenate in access but the dlookup function in access does not have the fourth argument as in the vlookup function in excel... that's what i need!
    Example
    Product code date bought price paid
    19483hsn2 10/7/2001 US$1

    what is the correct price according to the quotes in the other file for this product at the given date?

    See what I mean?




    Originally posted by Jerrie
    lol why concatanate? i still dont get the aim here; note that nothing is impossible in access since access and excel are like sisters;

    "I used vlookup to make a specific cell return the correct value for that product at the given time." <-- is this the aim?

    if so, whats the correct value? whats that value?

    either way, you can conatenate stuff in access; just do [CurrentDate] & [ProductCode]

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    what does that other file have; list the complete details
    nice example; lol, sorta feel like i'm stupid...but i how to do what you want;

    you're goin to compare that product code, price with the product code, price in the other table right? the dates dont matter?

  10. #10
    Join Date
    Mar 2002
    Posts
    32
    You can use wildcards in your dlookup criteria. That should make it work like a vlookup with the last option flagged.

    eg

    DLookup("field", "table", "[searchfield] like '*" & searchcriteria & "*'")

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Post a sample of one record FROM EACH FILE that you need to pull information from. Then post the desired result.

    You have un-normalized data that is not logically organized. This is why nobody can understand what you want, it doesn't make sense.

  12. #12
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    the dates do matter because for product A there are several quotes but for different periods. based on the product code and the price from the invoice, i need to obtain from the quotes file the correct price in order to compare if i paid the correct price or have overpaid for it.




    Originally posted by Jerrie
    what does that other file have; list the complete details
    nice example; lol, sorta feel like i'm stupid...but i how to do what you want;

    you're goin to compare that product code, price with the product code, price in the other table right? the dates dont matter?

  13. #13
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    Example below

    Quotes file
    Vendor Item Final price Request Date Expiration Date
    5311 32905082 0,04000 5/8/1999 21/1/2002

    Invoice file
    product code date of purchase price paid
    32905082 12/12/1999 0,039


    I've got over 650.000 entries to verify, therefore i need to make it work on it's own....




    Originally posted by marsoncunha
    the dates do matter because for product A there are several quotes but for different periods. based on the product code and the price from the invoice, i need to obtain from the quotes file the correct price in order to compare if i paid the correct price or have overpaid for it.

  14. #14
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    k i think i get what he means teddy; yea, it was sorta puzzlin to figure out what you want; correct me if im wrong;

    Table a
    Product_Code
    Date_Bought
    Product_Cost

    Table B
    Product_Code
    Date_Begin
    Date_End
    Product_Cost


    Now, there are duplicates for the Product_code in Table B, (but not for Table a?) i think there are no duplicates for Table a;

    for a specific product_code in tbla, he has to find between which set of date_being and date_end in tbleB the date_bought in tbla belongs to;
    once the date set is found, he has to compare the products_costs between both tables and return true if they are equal, and false if they dont

  15. #15
    Join Date
    Mar 2004
    Location
    Brazil
    Posts
    12
    That's right, but I don't need the T/F response but the actual number in tblb. There might be several times the same product in tbla with dates that match or don't, but that shouldn't be an issue.

    Originally posted by Jerrie
    k i think i get what he means teddy; yea, it was sorta puzzlin to figure out what you want; correct me if im wrong;

    Table a
    Product_Code
    Date_Bought
    Product_Cost

    Table B
    Product_Code
    Date_Begin
    Date_End
    Product_Cost


    Now, there are duplicates for the Product_code in Table B, (but not for Table a?) i think there are no duplicates for Table a;

    for a specific product_code in tbla, he has to find between which set of date_being and date_end in tbleB the date_bought in tbla belongs to;
    once the date set is found, he has to compare the products_costs between both tables and return true if they are equal, and false if they dont

Posting Permissions

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