Link to home
Start Free TrialLog in
Avatar of daugh016
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.

Avatar of rockiroads
rockiroads
Flag of United States of America image

U have to make use of IIF

assuming this is done in a query

Left(N.FirstName,   IIF(InStr(1,N.FirstName,' ')>0,InStr(1,N.FirstName,' '),Len(N.FirstName))

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
Avatar of Carl2002
Carl2002

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?
Avatar of daugh016

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.

ASKER CERTIFIED SOLUTION
Avatar of Carl2002
Carl2002

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Carl.  That worked