Left Join error, look field has 1 extra character

[BustyAshley]

FROM [Total Billables Table] LEFT JOIN [10 Account Mappings] ON (nz([Total Billables Table].[Contract #],"")) = nz([10 Account Mappings].[SiteID],"");


I'm using the above to do a lookup but I realized my [Contract #] has 10 characters vs my [siteID] only having 9.


Example would be [Contract #]
WESTMAR01G
WESTMAR01L

Return values that match with [SiteID]
WESTMAR01

The last character doesn't matter for this lookup. How to I set it to look for only the 9 that matter?

 

[ByteMyzer]

You might try this for your FROM/JOIN clause:

...
FROM (
    SELECT *, Left([Contract #], 9) AS SiteID
    FROM [Total Billables Table]) AS [Total Billables Table]
LEFT JOIN [10 Account Mappings]
    ON [Total Billables Table].SiteID = [10 Account Mappings].SiteID;

 

[gemma-the-husky]

but really, what is going on that makes you need to do partial matches?

where does the G and L suffix come from?

 

[CJ_London]

try

SELECT *
FROM [Total Billables Table] LEFT JOIN [10 Account Mappings] ON left([Total Billables Table].[Contract #],9) = [10 Account Mappings].[SiteID]

although you won't be able to edit this in the query GUI - only in SQL

PS - using # and othe non alphanumeric chars can cause problems, even if you use square brackets, strongly advise you remove them

 

[BustyAshley]

This worked. I didn't need the 'nz' part of the look up because every record contained a contract ID. Thx