Using Intr function in query getting data type mismatch

[gammaman]

I have two tables both contaning a Last_Name Field. In one of the tables name suffixes are appened to the end of the last name and in the other table it is stored in a seperate field.



Ex. Table 1: Last_Name = Goode Jr
Table 2: Last_Name = Goode

I want query to say Goode = Goode

For the last_name in the Critera Section I have: table2.last_name = Left(Instr(table1.last_name, ' '),Instr-1)

I get a data type mismatch.

[JoeM]

A few issues:

1. You are missing all your arguments for your second INSTR function.

2. If there are no spaces in a name, your formula would return an error (INSTR would return 0, so you would be trying to return the LEFT most -1 (negative one) spaces.

What I would do is create a query that has a calculated field to return the LastName, up to the first space (if one exists). Then use this query in your other query, joining on this new calculated field.
That calculated field might look something like this:

Adjusted_Last_Name: IIf(InStr([table1].[Last_Name],' ')>0,Left([Table1].[Last_Name],InStr([table1].[Last_Name],' ')-1),[table1].[Last_Name])