I have a field in SQL table that houses a name (First, Last and Middle) I want a to write a query to extract this data into three seperate fields. I wrote the query and it works fine in MS Access 97/2000, but will not run in SQL. But there slite problem when it runs in Access, if there is no middle initial, it returns the middle initial value as #error.
SELECT
Left([BName],InStr(1,[BName],” “)-1) AS [First Name],
Trim(Mid([BName],InStr(1,[BName],” “)+1,InStr(InStr(1,[BName],” “)+1,[BName],” “)-InStr(1,[BName],” “))) AS [Middle Initial],
IIf(InStr(InStr([BName],” “)+1,[BName],” “)<>0, Right([BName],Len([BName])-InStr(InStr([BName],” “)+1,[BName],” “)),Right([BName],Len([BName])-InStr([BName],” “))) AS [Last Name]
FROM Student Name;
Thank you,