Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    dharmik is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2010
    Posts
    1

    Query to work as a Vlookup function

    Given:
    One table with following fields:
    System, Component, Anomaly number, Status, Priority.

    I have a form with first two fields as system and component. You can select each with from drop down menus.


    Question:
    I would like to create more fields in this form, where I can choose system and component and display anomaly numbers associated to them and their respective status and priority.

    I can do this in excel using VLOOKUP. Tried using DLOOKUP in access but cannot get it to work.

    Please help. Also, I am newbie using Access, so keep that in mind if you find my question to be very basic.

    Regards,
    D

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hi dharmik,

    It depends on what you intend to do with the resulting records, but the easiest thing to do is run a query that uses your selected fields from the form as criteria for the query result. (No need for VLOOKUP).

    Cheers,

  3. #3
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    hi i also have a problem with the dlookup function.i have two tables.one table has the shipping id and a waybill number and the other only has a waybill number.my task is to make a lookup that will compare the waybill number of table two to the one in table one.if there waybill number in table one is not on table two it should give me this statement"not on list"...i hope that i am making sense

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Try this:

    Code:
    SELECT Table1.ShippingID, Table1.Waybillnr, Table2.WaybillNr, IIf([table2].[WaybillNr] Is Not Null,"In List","Not In List") AS InList
    FROM Table1 LEFT JOIN Table2 ON Table1.Waybillnr = Table2.WaybillNr;
    Change your fieldnames and table names to agree with your db.

    Alan

  5. #5
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    IT worked thank you very much...i am still new at this where do i go to rate you and how do i mark the thread as solved?

  6. #6
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    To rate, click on the scales in the upper right hand corner. To mark solved, go to the original posting and click on Thread tools. Glad you have it solved.

  7. #7
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    thank you once more

  8. #8
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    Allan i just realised that the query you helped me design only gives me what is on the list.
    Let me take you throw my problem again.
    I have two tablesStore units(T1) and Master shipping table(T2).
    I have to search the waybillnr that is on T1 on T2.If the waybillnr is availible in both T1 and T2,Access should give me the corresponding shipping ID(found in the store units table).
    If not it should say"Not on List").

  9. #9
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    In your IIF statement change the "In List" to read [ShippingID]. This will put the shipping ID in the field if waybill nr is not null.

  10. #10
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    the query is only showing me the waybill numbers that have shipping ID's.I also want it to show me the "not in List".
    remember that we are comparing two tables to see how many waybill numbers and how many do not.

  11. #11
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    I'm confused. Perhaps you need to post a copy of your db so that I can see exactly what you are looking at. Be sure to run a compact and repair and dummy up any confidential information. Include only a sample of your data. We do not need to see all the data. Keep the db small by doing this.

    Alan

  12. #12
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    okay.I'll do that

  13. #13
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52

    attached example

    hi.i have attached demo's from excel of the two tables that i imported into access.
    I have two store check(T1) and shipping master table(T2).
    I have to search the billnr that is on T1 on T2.If the billnr is availible in both T1 and T2,Access should give me the corresponding shipping ID(found in the store units table).
    If not it should say"Not on List").

  14. #14
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Is this what you are looking for? See Attached.

  15. #15
    funi.t is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    52
    i have already done that part.My problem is checking which bill numbers on the store check appear on the shipping master.if the bill numbers appear on the shipping master i want the shipping id to be added in the store check table,if not it should give me:"not on list".
    I have done it on excel.i have attached the spreadsheet.

    thanks a lot for your help

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

Similar Threads

  1. Query doesn't work when launched by code
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-24-2009, 09:52 AM
  2. Replies: 0
    Last Post: 02-27-2009, 01:39 PM
  3. Replies: 0
    Last Post: 12-28-2008, 01:56 PM
  4. Query using count function
    By wasim_sono in forum Queries
    Replies: 0
    Last Post: 11-28-2007, 03:16 AM
  5. Nested IIf query will not work
    By ddog171 in forum Queries
    Replies: 1
    Last Post: 06-20-2006, 02:03 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