Substring

[Kieran]

I want to join 2 tables using 1 field. however the field
in table 1 uses trade # plus one extra digit and the
field in table 2 uses just trade #. Using SQL I would use
a substring but that doesn't appear to work in access, is
there a way to do it in the design view.

 

[Ken Snell [MVP]]

To do it in design view, you'd build a query that has a calculated field in
it that strips the last digit from the field. Then build a second query that
uses this first query as a source table and the other table as a source
table, and join them on the calculated field in the first query and the
normal field in the table.

Otherwise, you can build the query in one step using SQL view. Use a
non-equi-join (ON Left([FirstTable.FieldName], Len([FirstTable.FieldName]) -
1) = SecondTable.FieldName).