I am using Access project file to connect to SQL Server 2000.

In my query I want to display First_name and Last_name from a Name field. I do this by copying left and right of a ' ' (space) to separate first and last name:

SELECT LEFT(Name,CHARINDEX(' ',Name)-1) AS FNAME, RIGHT(Name,LEN(Name)-CHARINDEX(' ',Name)) AS LNAME
FROM tblClients
...etc...

On occasion I get an error (I think) when a Name does not contain a ' ' (space) character.

What I'd like to do is first compare (using something like IIF function) if there is a ' ' in the string:

SELECT IIf(CHARINDEX(' ',Name) > 0), LEFT(Name,CHARINDEX(' ',Name)-1), Name) AS FNAME, IIf(CHARINDEX(' ',Name) > 0), RIGHT(Name,LEN(Name)-CHARINDEX(' ',Name)),Name) AS LNAME
FROM tblClients

So my question: Is there a good way to do this? Seems IIF is not recognized as a function name in Access projects.

Try this:

_____________________________

SELECT
CASE
     WHEN CHARINDEX(' ',Name) > 0)
          THEN
              LEFT(Name,CHARINDEX(' ',Name)-1)
     ELSE Name
END
AS FNAME,
CASE
     WHEN CHARINDEX(' ',Name) > 0)
         THEN
             RIGHT(Name,LEN(Name)-CHARINDEX(' ',Name))
         ELSE Name
END
AS LNAME,
FROM tblClients
___________________

This uses the CASE logic. Look it up in Books On Line. It can be a powerful statement to use.



Sal

Thank you for your quick reply! That's a useful bit to keep in my SQL bag of tricks.

The SQL editor is accepting the syntax. But I think I still have a logic error. The error message is:

"Invalid length parameter passed to the Substring function."

Which is the original error I got and what prompted me to put in the extra logic...

I'm thinking maybe it is if the string is NULL or "". (???)

Is there a way to test additional conditions? Such as:

WHEN CHARINDEX(' ',Name) > 0) OR (Name = NULL)

Please excuse my ignorance... I was planning on creating a scalar text function but couldn't get past the syntax.

I figured it out...

The error condition is when the Name is " " (one character string that is a space) which happens frequently in databases, due to human (lamer) error.

Anyways... Thanks again for your help.