This example was constructed using the Query Design Grid. It assumes the existence of a Table called NAMES with a Field called Lastname.
SELECT LANDLORDS.LastName, Left([Lastname],5) AS Expr1
FROM LANDLORDS;
To used in VBA Code remove the semicolon and place in Quotes:
"SELECT LANDLORDS.LastName, Left([Lastname],5) AS Expr1
FROM LANDLORDS"
Try Ltrim, Rtrim and Trim to remove spaces.
My SAMS Teach Yourself SQL says that LTRIM can be used to remove any leading string value from a string but I haven't made it work in VBA.
Regards
Rod