Results 1 to 10 of 10
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    DLOOKUP Function for Multiple Criteria.

    Hi

    I have a main form named as" Certificate Details" which presents the table "CertificateDetails" and interested to pull the values from another table named as" QualityInspection" based on matching of three fields criteria.

    CertificateDetails.InspectionDate =QualityInspection.InspectionDate
    CertificateDetails.PDate = QualityInspection.PDate
    CertificateDetails.Product = QualityInspection.Product

    The data must be pulled from below fields of table "QualityInspection" and filled up in the fields of table "CertificateDetails":

    Note: The fields name are same in the both tables.

    AELength
    AIDSpigot
    AIDSocket
    AODSpigot
    AODSocket
    AWTSpigot
    AWTSocket
    ADSocket
    ASqEnds
    ADvStraightness



    Kindly advise the needful.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then you need to use a query. JOin the tables.

  3. #3
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    ... Or perhaps you could use a DLookup?

  4. #4
    MatthewR is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    118
    Oh, wait. I misunderstood. Yeah, you should definitely make a relationship between the tables, and base the form on a query. But if you did want to do a DLookup with multiple criteria, which I don't recommend, I think it would be something like:

    AELength = DLookup("AELength", "QualityInspection", "InspectionDate = " & InspectionDate & " And PDate = " & PDate & " And Product = '" & Product & "'"

    AIDSpigot = DLookup("AIDSpigot", "QualityInspection", "InspectionDate = " & InspectionDate & " And PDate = " & PDate & " And Product = '" & Product & "'"

    ...and so on.

    (This assumes InspectionDate and PDate are date fields and Product is a String field)

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Based on what you said in Post #1, why do you have two tables that are identical?
    You are storing duplicate data, which kinda defeats the purpose of an RDBMS.


    I don't know your project requirements, but it sure sounds like you have a table structure design flaw.

  6. #6
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi Every body

    Thanks for your feedback. As Mr. Steve asked, let me explain why there are two tables with almost same fields.

    QualityInspection" table is the main one which recorded the transactions on daily basis when products are inspected (10 products) and there will be years data for each product whereas "CertificateDetails" table recorded the transactions when products are delivered to customers and each delivery will have a quality certificate mentioning the measurements of product and this can be after months or years.

    So when ever store man told the guy that he have this product of this production date then he have to extract all the information related to that on certificate and store the records again each customer.

    Hope its clear and expects further your feedback.

  7. #7
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Mr. Steve

    what do you say for the above explanation?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would have to see your current table structure. I know there is a lot you haven't said because it would be a lot of typing.
    I still think you have table design issues.........

    The data must be pulled from below fields of table "QualityInspection" and filled up in the fields of table "CertificateDetails":
    If all you are doing is copying one record (or data from specific fields in one record) into another record, what is the point? Manually typing in new readings and comparing records I understand. But just coping records.... ??

    I would use an update query or maybe VBA to copy data from one record in table "QualityInspection" and insert it into "CertificateDetails".


    I would try creating a SELECT query with criteria so that only the record you want to duplicate is displayed. Then change the SELECT query to an Append query.

  9. #9
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Dear Sir

    Thanks for your inputs and i am sorry for being away for such long time.

    As i explained earlier, the "QualityInspection" table recorded the data on daily basis when products are inspected during converting to Finish Goods and then goods are stored. During the time of delivery which may happen after few days or after months, a "Quality Certificate is required to be attached with each delivery note including its on heading information like Certificatedate, CertifcateNo, DeliveryNoteNo, Product, PrdnDate, Inspectiondate and will also include some common fields which are also in the table of "QualityInspection"

    Therefore, the purpose is to reduce the manual data entry and fetch the data of common fields to fetch into the "QualityCertificate" table based on matching the criteria of Product, PrdnDate.

    So the solution shall be, if the operator typed in the Product, PrdnDate in the fields of "QualityCertificate" then a list of available records from table ""QualityInspection" shall pop up so the operator can select the most favorable Product, PrdnDate to copy the information in the rest of the fields in the table "QualityCertificate".


    Kindly advise the solution for this.

  10. #10
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    I would use an update query or maybe VBA to copy data from one record in table "QualityInspection" and insert it into "CertificateDetails".


    I would try creating a SELECT query with criteria so that only the record you want to duplicate is displayed. Then change the SELECT query to an Append query.
    I think this idea shall be best suitable if works.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-11-2016, 01:03 PM
  2. Replies: 15
    Last Post: 07-09-2015, 01:39 AM
  3. Help with multiple criteria DLookup
    By jtm013 in forum Programming
    Replies: 7
    Last Post: 04-23-2015, 03:16 PM
  4. Replies: 4
    Last Post: 06-30-2014, 02:53 PM
  5. Multiple criteria in DLookup
    By crowegreg in forum Forms
    Replies: 7
    Last Post: 06-22-2011, 01:47 AM

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