daugh016
asked on
MS Access -- Left() and InStr() Problem
Here is my issue:
I am using the following in select statement:
Left(N.FirstName,InStr(1,N .FirstName ,' '))
It works fine when N.FirstName is two words but return nothing when it is only one word.
I understand because InStr(1,N.FirstName,' ') is returning '0' then I am tryng to get everything to the left of '0'. Is there away to put in some sort of if statement to take this into account.
I am using the following in select statement:
Left(N.FirstName,InStr(1,N
It works fine when N.FirstName is two words but return nothing when it is only one word.
I understand because InStr(1,N.FirstName,' ') is returning '0' then I am tryng to get everything to the left of '0'. Is there away to put in some sort of if statement to take this into account.
ok, my mistake. Out of practice I give the answer but dont always give an explanation. Sorry about this, I hadnt realise what I do was considered Post Tagging
So if u need an explanation, let me know
So if u need an explanation, let me know
It is returning nothing when N.Firstname is one word because of the " " at the end of the statement. The expression is specifically searching for the first space, if there isn't one it will fall down.
Can you explain a little more what you are trying to achieve?
Can you explain a little more what you are trying to achieve?
ASKER
Carl,
If someones first name is two words, I just want the first one.
Example if someones name is Mike, I want it to show Mike.
Example if someones name is John Mark, I want it to show John.
Rockiroads,
I couldn't get it to work. It says missing operator.
If someones first name is two words, I just want the first one.
Example if someones name is Mike, I want it to show Mike.
Example if someones name is John Mark, I want it to show John.
Rockiroads,
I couldn't get it to work. It says missing operator.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Carl. That worked
assuming this is done in a query
Left(N.FirstName, IIF(InStr(1,N.FirstName,' ')>0,InStr(1,N.FirstName,'