none
Running Report in Ms access Generates incorrect result RRS feed

  • Question

  • Hi, I have form with 3 textboxes. The user can enter values in either of them or all of them. Upon entering the values and clicking a button a report is generated.
    Now the problem is if i enter all the 3 values in the textbox, instead of getting 1 single record, i'm getting multiple records.

              "INSERT INTO TmpContainersForPLReport " & _
                        "SELECT Containers.*,Shipments.ShipmentInvoiceNumber FROM Containers INNER JOIN Shipments ON (Containers.ShipmentID = Shipments.ShipmentID)  WHERE Containers.PackinglistentryDate = #" & TxtEntryDate & "# " & _
                        " OR (Containers.SalesOrderNumber = '" & txtSalesOrderNumber & "') OR (Containers.PONumber = '" & TxtPONumber & "') OR (Shipments.ShipmentInvoiceNumber = '" & txtShipmentInvNum & "' )" & _
                        " OR ((Containers.SalesOrderNumber = '" & txtSalesOrderNumber & "')And(Containers.PONumber = '" & TxtPONumber & "') And (Shipments.ShipmentInvoiceNumber = '" & txtShipmentInvNum & "')) " & _
                        " OR (Containers.SalesOrderNumber = '" & txtSalesOrderNumber & "' And Containers.PONumber = '" & TxtPONumber & "')" & _
                        " OR (Containers.PONumber = '" & TxtPONumber & "' And Shipments.ShipmentInvoiceNumber = '" & txtShipmentInvNum & "')" & _
                        " OR (Containers.SalesOrderNumber = '" & txtSalesOrderNumber & "' And  Shipments.ShipmentInvoiceNumber = '" & txtShipmentInvNum & "' )"
      
    The final report is getting values from TmpContainersForPLReport.

    Thankyou
    Tuesday, June 6, 2017 4:46 PM

All replies

  • Hi,

    I wonder if it has anything to do with you using ORs rather than ANDs in your WHERE clause.

    Hope it helps...

    Tuesday, June 6, 2017 5:07 PM
  • Can you please elaborate. I'm new to SQL
    Tuesday, June 6, 2017 5:12 PM
  • I'll try...

    When you combine multiple criteria or condition in a WHERE clause, you can use either OR or AND. OR will return the record if any of the conditions is TRUE. Whereas, AND will only return the record if ALL conditions are TRUE.

    In other words, using AND is more restrictive (less records in the result) than using OR.

    Hope it makes sense...

    Tuesday, June 6, 2017 5:22 PM
  • Yes. It did make sense. Thankyou for taking the effort.
    Tuesday, June 6, 2017 5:33 PM
  • You're welcome. Hope it helps you find a solution to your problem. Good luck with your project.
    Tuesday, June 6, 2017 5:38 PM
  • Yes. It did make sense. Thankyou for taking the effort.

    Hello,

    If you issue has been resolved, I suggest you mark helpful post as answer to close this thread. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 7, 2017 2:52 AM
    Moderator