creating join with only the first 4 characters of a field

J

John Spencer

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
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help creating a search form in Access XP 0
Remove all formatting charactersf except for paragraph and cr/lf 0
Problem with MS Word paragraph and character styles: format getting messed up 2
Internal Data Integrity (Type 4) Details Needed 1
Mail Merge Directory Conditional Page Break 0
Sub projects added as read only in Master file which are saved on sharepoint 1
SQL query with join on substring 5
No option to create a list in SharePoint Site 0

Top