Link on part of field 1

[MICKI0220]

I have an issue where I need to link two tables to do a query. My problem is that I have two different types of invoice numbers that have to be linked together..... lets take two tables, we will call them tableA and tableB.....tableA has an invoice field that has data like E-7612....tableB has an invoice field that the data has been combined like E-761223456.....I need to create a Vendor ID link on both tables (an additional field) with a combo link of Invoice id ,but only the first 6 alphanumeric characters of tableB.inovice.....I hope I am making sense

Thanks for your help

 

[PHV]

one way:
tableB.[invoice field] Like tableA.[invoice field] & '*'
another way:
tableA.[invoice field] = Left(tableB.[invoice field],6)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

Thanks for a quick response. I'll try this.

 

[MICKI0220]

I get a syntax error....this is my code


SELECT dbo_tblApCheckHist.VendorID, dbo_tblApCheckHist.InvoiceNum, dbo_tblApCheckHist.GrossAmtDue, dbo_tblApOpenInvoice.DistCode, dbo_tblApOpenInvoice.InvoiceDate
FROM dbo_tblApCheckHist INNER JOIN dbo_tblApOpenInvoice ON (dbo_tblApCheckHist.InvoiceNum = left([dbo_tblApOpenInvoice.InvoiceNum],6) AND (dbo_tblApCheckHist.VendorID = dbo_tblApOpenInvoice.VendorID);


Thanks for your help

 

[PHV]

What about this ?

SELECT C.VendorID, C.InvoiceNum, C.GrossAmtDue, O.DistCode, O.InvoiceDate
FROM dbo_tblApCheckHist C
INNER JOIN dbo_tblApOpenInvoice O ON C.VendorID = O.VendorID
WHERE C.InvoiceNum = Left(O.InvoiceNum,6)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

That did it, Thank you very much.

 

[MICKI0220]

I need to do a similiar thing, However I need to link on everything except the last two characters.

See below....The field "tbl_ats_Comm_Draw_Hist.Key" is the field that I need to only = everything but the last two characters....I am trying to split one invoice into two by putting a -1 and I -2 on the end, but I use the below query to weed out any invoices already processed.


INSERT INTO ARTAP ( Seq_No, CustID, InvNum, TranType, [Date], Amount, ChkNum, Contract_Type, Rep_1, Rep_2, CommStDate )" & _
"SELECT ARTAP_IMPORT.Seq_No, ARTAP_IMPORT.CustID, ARTAP_IMPORT.InvNum, ARTAP_IMPORT.TranType, ARTAP_IMPORT.Date, ARTAP_IMPORT.Amount, " & _
"ARTAP_IMPORT.ChkNum, ARTAP_IMPORT.Contract_Type, ARTAP_IMPORT.Rep_1, ARTAP_IMPORT.Rep_2, ARTAP_IMPORT.CommStDate " & _
"FROM ARTAP_IMPORT LEFT JOIN tbl_ats_Comm_Draw_Hist ON ARTAP_IMPORT.Seq_No = tbl_ats_Comm_Draw_Hist.Key " & _
"WHERE (((tbl_ats_Comm_Draw_Hist.Key) Is Null))

 

[Andrzejek]

How about:
[tt][blue]
Left([/blue]tbl_ats_Comm_Draw_Hist.Key, [red]Len([/red]tbl_ats_Comm_Draw_Hist.Key[red])[/red]-2[blue])[/blue][/tt]

Have fun.

---- Andy