Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query Combine

    Hello,
    I have the following SELECT query:

    SELECT Invoices.Invoice, Invoices.Invoice_File_Links, Invoices.Status
    FROM Invoices
    WHERE (((Invoices.Invoice) Like "ADJ*") AND ((Invoices.Invoice_File_Links) Is Null) AND ((Invoices.Status)="Fully paid"));

    There is a now a request to include invoices like "BD*". So, I've modified my query to the following:

    SELECT Invoices.Invoice, Invoices.Invoice_File_Links, Invoices.Status
    FROM Invoices
    WHERE (((Invoices.Invoice) Like "ADJ*" Or (Invoices.Invoice) Like "BD*") AND ((Invoices.Invoice_File_Links) Is Null) AND ((Invoices.Status)="Fully paid"));

    I then used an UPDATE query to update the file link location like so (obviously with the prior select query):

    UPDATE audit_invoiceFileLinks SET audit_invoiceFileLinks.Invoice_File_Links = "\\misc\sites\CS\Adjustments\AdjustmentFormsArchiv e\" & "" & [Invoice] & "" & ".xml";

    Is there any way to modify the UPDATE query to update invoices equal to "BD*" to the following location and leaving the "ADJ*" location the same: \\misc\sites\BR\BoardDisbursementsArchive\" & "" & [Invoice] & "" & ".xml

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think the easiest option would be to have two separate update queries. One that does the like ADJ* Invoices, and another to do the BD* ones.

    Otherwise you need to put an IIF statement in the Set Expression. Something like this.

    SET audit_invoiceFileLinks.Invoice_File_Links = iif([invoice] like "ADJ*", "\\misc\sites\CS\Adjustments\AdjustmentFormsAr chiv e\" & "" & [Invoice] & "" & ".xml", " \\misc\sites\BR\BoardDisbursementsArchive\" & "" & [Invoice] & "" & ".xml ");
    Regards
    John



Posting Permissions

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