I made this work now, partially.
However I have a problem with the vendor name last 6 digits extraction "VendorName:mid(vendor_ID,instrrev(vendor_ID," ")+1)"
Some vendors in the table are in a numerical form rather than a combination of letters and numbers. When there is a numerical VendorName, the name (which is the ID) is preceded by 4 zeros.
However, in the other table where the vendor query pulls the full vendor name (aka Vendor ID), the zeros have been stripped already.
For example in table 1 it would be "0000142719"
In table 2 it would be "COCA COLA 142719" instead of "COCA COLA 0000142719"
The query is looking for a value equal to a string but when the value is on one side a 6 digits number and on the other side a 10 digits number (where the first 4 are zeros), it does not find a match.
The current query SQL selection statement is
Code:
SELECT DISTINCT Forecasts.[Vendor ID], Mid([vendor ID],InStrRev([vendor ID]," ")+1) AS VendorName
How can I rewrite this to be like
IF SELECT DISTINCT Forecasts.[Vendor ID], Mid([vendor ID],InStrRev([vendor ID]," ")+1) AS VendorName IS NUMERICAL
THEN EXTRACT THE 6 DIGITS AND ADD 4 ZEROS IN FRONT
Or, in the other query that is looking for the match, how could I tell it to look for a partial match of the last 6 characters only?
The current statement is as on the screen shot, I would need to change what is highlighted.
So in the numerical example [dbo_t_Scheduled_Order].[Vendor_Name] is looking for a value of "0000142719" in a query where the value is "142719" then does not find the match.
When the vendor name is like "COCA COLA C42719" then the vendor query returns C42719 as VendorName and the vendor name in table 1 is C42719 so it finds the match.