How do I trim a text string down to the first two words?

[CTHabitat]

I am trying to merge two Db tables in Access 2007. I am working with tables that vary in the detail given for the Scientific names for plants (my only real cross reference that is useable). For example, Table1 lists the Scientific name as "Alnus incana (L.) Moench ssp. tenuifolia (Nutt.) Breitung" while Table2 lists it as "Alnus incana". I am trying to create a field in Table1 with the trimmed Sci Names matching the Table2(i.e., Alnus incana). What function can I use to do this, and how might it be written?

For simplicity's sake we will call the fields "Detailed" in "Table1" and "Simple" in "Table2".

Thanks for any help,

C

[Seth Schrock]

It would be better to not store the slimmed down version in your table 1 (see Database Normalization). Instead what you can do is create a query that does the calculation for you when you need it and then reference the query instead of the table. To do this, you will use a combination of the Left() and InStr() functions.

SELECT Left(Detailed, (InStr(InStr(Detailed, " ") + 1, Detailed, " "))-1) As NewField
FROM Table1

This will get everything up to, but not including the second space.

[CTHabitat]

Thanks, Worked Perfectly!

C

[Seth Schrock]

No problem. Glad to be able to help. If you would please choose post #2 as the best answer, it will help others who are looking for the answer to a similar question.