J John Spencer Sep 28, 2007 #2 the best way to join on just 4 characters is to do it in the SQL view. If you want to do it in the query design view (the grid). You can build a query on the first table with the calculated field Field: JoinThis: Left(SomeField,4) Save that query Now build a query on the second table doing the same thing Now build a query with the two saved queries as the sources and join on the two calculated fields. Alternative Build the query on the two tables and join on the fields that you need. Switch to SQL view and find the clause TableA INNER JOIN TableB ON TableA.Somefield =TableB.AnotherField Edit that phrase to TableA INNER JOIN TableB ON Left(TableA.Somefield,4) = Left(TableB.AnotherField,4) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County ..
the best way to join on just 4 characters is to do it in the SQL view. If you want to do it in the query design view (the grid). You can build a query on the first table with the calculated field Field: JoinThis: Left(SomeField,4) Save that query Now build a query on the second table doing the same thing Now build a query with the two saved queries as the sources and join on the two calculated fields. Alternative Build the query on the two tables and join on the fields that you need. Switch to SQL view and find the clause TableA INNER JOIN TableB ON TableA.Somefield =TableB.AnotherField Edit that phrase to TableA INNER JOIN TableB ON Left(TableA.Somefield,4) = Left(TableB.AnotherField,4) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County ..
