Results 1 to 6 of 6
  1. #1
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    62

    Trying to do a Form Parameter Search using a UNION Query - have run out of ideas

    Hi Folks,



    I've built a simple search form that allows end users to input a value in a form, e.g. in a textbox. A query then uses this parameter as a criteria. I have no problem doing this in QBE but lately, I am trying to use a UNION query and the SQL is way over my head.

    Please read the following before looking at the attachment:

    1) I have two tables on which I am trying (and failing) to use a UNION query to pull the two datasets together... see tblINVOICE and tblINVOICE-ARCHIVE.

    2) Open frmSearch and see that the Invoices shown are only coming from tblInvoice.

    3) I am trying to get invoice records from both tblInvoice and tblInvoiceArchive to appear in the search results. I have deleted my previous Union Query Attempts!

    4) Finally, please note that i am using the following parameters as criteria:
    Between [START Date] and [END Date] ( and check box [Paid])

    Please help if you can,

    much appreciated,

    Tiny

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why are you archiving records to a separate table? Why not just another field to set status of record and use that field to exclude from forms and reports?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    62
    Yes, I understand ur sentiments. Regardless of this, I Wud rather maintain the two tables I have got.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As far as I can see, the UNION should be:

    SELECT InvoiceID, CustomerID, [Date], Paid, "Current" AS Source FROM tblInvoice
    UNION SELECT InvoiceID, CustomerID, [Date], Paid, "Archived" FROM tblInvoiceArchive;

    Then use that query as source for subsequent queries.

    Date is a reserved word. Should avoid reserved words as names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Dec 2011
    Posts
    62
    Many thanks June...this has worked fine. I guess this is what's called a Sub-Query?!

    thanks again.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, that is one way to put it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 7
    Last Post: 07-11-2013, 10:27 AM
  2. Query with parameter from Form Textbox
    By Juan4412 in forum Queries
    Replies: 1
    Last Post: 07-10-2013, 02:33 PM
  3. Replies: 1
    Last Post: 06-19-2013, 12:32 AM
  4. Replies: 1
    Last Post: 02-28-2013, 01:20 PM
  5. Replies: 1
    Last Post: 03-06-2012, 10:07 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