MS ACCESS: query criteria using "like"

Question

I'm trying to run a query to compare two tables based on a field called "Invoice Number". The criteria for that field in the query is controlled by a textbox in a form. The problem is the Invoice Numbers from the two tables have different formats, which i know is dumb. Our vendor will repeat invoice numbers every year for some reason so we have to add an underscore and date code on our end so that we don't run into accounting issues when we want to receive units into our system. For example, they will send an Invoice named A0078JUN one year, and the next year they will repeat that invoice number so if I were to receive that invoice today, I would have to add "_082721" so that we could have a unique invoice number for that shipment. In this example, our invoice number would look like this "A0078JUN_082721". Using this invoice as an example, i would want to query everything from both tables that begin with "A0078JUN", which is what I would type in the textbox. Any suggestions? Using access 2016

Answer 1

You could use:

Parameters
    [Forms]![YourFormName]![SelectInvoiceNumber] Text;
Select 
    T1.*, T2.*
From 
    T1, T2
Where 
    T1.InvoiceNumber = [Forms]![YourFormName]![SelectInvoiceNumber]
    And
    T2.InvoiceNumber Like [Forms]![YourFormName]![SelectInvoiceNumber] & "*"