Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup function (Access 2000)

    In a report i have 2 fields calclulating the sales in different ways, but i want to have a third, an unbound control looking up for the figure in a table called products and in a field called stock. Since this figure is static i want to look up the table depending on the number of the product in the list of the products that the report shows.The name of my unbound control is Text32, and the fields on the reports are as following

    ProdictID ProductsOnConsigmnet ProductsSold Stock

    i want to find out what is the figure of the stock in the table products. The table products has a field caled productid which i use in my report. Heres is an example:
    On Cosnginment Sold Stock

    1. Round Apple 40 20


    I have tried the following code but Access does not accept it:

    DLookup("stock", "products", "productid = " & Me.productid)
    I do not know shall i refer to the Productid, or to the name of my unbound control which is Text32.

    May i ask for help?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup function (Access 2000)

    In the control source of text32 put
    If productid is numeric :
    <pre>=DLookup("stock", "products", "productid = " & productid)</pre>

    if productid is text :
    <pre>=DLookup("stock", "products", "productid = '" & productid & "'")</pre>

    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup function (Access 2000)

    Thank you very much for your kind reply. With me ProductID is numeric.To my regret however i still
    cannot display my DLookup on the report.Acces denies me that. I have put the following in the control source of Text32:
    =DLookup("stock", "products", "productid = " & productid)
    I wonder where my mistake me be. I do indeed have a table products, and a field called stock.
    Is it possible that i have some other mistakes?Something with my report?Or may be i just cannot manage to enter the code in the right way.Anyway i will be grateful for any comments.I am very sorry that i cannot manage to do the coding properly

    Best regards

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup function (Access 2000)

    Did you have ProductId in the detail of you report ?
    If not ad it and if you don't want to print it, set the visible property of it to No.
    I have attached a little mdb with a sample.
    Attached Files Attached Files
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup function (Access 2000)

    Now it works ! Thank you very much and may i wish you a happy and pleasant week

    best regards

    Markus

Posting Permissions

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