Update Query InStr function

[neilmcmor]

I am having a bit of a problem with my update query. i have a field that shows a forename. i am importing data from an excel file. The forename populates with forename and middle names and they are seperated by spaces as opposed to commas. I have used the following InStr function in my update query however it works fine when the records forename field has a middle name but it deletes all data in the records forename field if it contains only one name which many do. How can I adjust the function to ignore those records that do not hold more than one name in the forename field. As you can imagine some forename and middle name combinations hold many names.
For instance the filad may have Ivor as a name which I would want to keep But if the Field showed Ivor Bigun then Bigun needs deleting.

Left([Forename],InStr([Forename]," "))

 

[Guus2005]

Use an IIF statement.

Replace

InStr([Forename]," ")

into

iif(InStr([Forename]," ")=0;Len([Forename]);InStr([Forename]," "))

so it becomes:

Left([Forename],iif(InStr([Forename]," ")=0;Len([Forename]);InStr([Forename]," ")))

Enjoy!

 

[neilmcmor]

Thanks

Thanks Guss. I had to change your semi-colons to commas, (as below), but worked beautifully after that. thanks for taking the time.

Left([Forename],iif(InStr([Forename]," ")=0,Len([Forename]),InStr([Forename]," ")))