Excel Formula Needed in Access

[bmschaeffer]

I have a formula in excel that produces the text before the second "space" that I need to use in access.

This is my excel formula:


=IF((LEN(C6)-LEN(SUBSTITUTE(C6," ",""))<=1),C6,(LEFT(C6,FIND(" ",C6,FIND(" ",C6,1)+1)-1)))

This is what I tried in access, but I am getting #Error:
Traveler: IIf((Len([Vendor])-Len(Replace([Vendor]," ",""))<=1),[Vendor],(Left([Vendor],InStr(" ",[Vendor],InStr(" ",[Vendor],1)+1)-1)))

Please help!

[alansidman]

Your issue is the syntax for the Instr Function. Look here for the proper syntax:

http://www.techonthenet.com/access/f...ring/instr.php

Alan

[bmschaeffer]

The first argument of the IIf function works fine if the string is 2 words. I am struggling with the FALSE argument of the IIf function. For a string with more than 2 words I need everything before the second space. I am only getting everything before the first space with this formula.

Traveler: IIf((Len([Vendor1])-Len(Replace([Vendor1]," ",""))<=1),[Vendor1],(Left([Vendor1],(InStr(1,[Vendor1]," ")))))

[alansidman]

You will have to add the Mid function to your formula to get the second space identified. Your starting position will be the formula for the first space plus 1. What I usually do with these type of comples expressions is put each part in a different field in the query and then join them with concatenation and then hide the helper fields or then rebuild the expression with the parts. A little tedious, but once complete a feeling of satisfaction.

Look here
http://www.techonthenet.com/access/f...string/mid.php

[bmschaeffer]

SOLVED IT! Thank you for your help