Hello All,

I have a question in database.

Our database is SQL server 2000. Nomally we convert SQL server 2000 to Access
for user creating Access report by using Access form and report.

It looks like Access database is different with the Access database which
convert form SQL server( It called Access Project, I searched website).

For example, I have a name field in Access database, I can use the following
code to split name to last name and first_middle name. (If no middle name,
select left from the first space as first_middle_name, otherwise select left
from second space as first_middle_name, as well as select from Right for last
name)

Database name: name
Table name: name

SELECT name.name,

IIf(InStr(InStr(," ")+1,," ")=0,Left(,InStr(," ")),Left(,InStr(InStr(,"
")+1,," "))) AS F_M_Name,

IIf(InStr(InStr(," ")+1,," ")=0,Right(,Len()-InStr(,"
")),Right(,Len()-InStr(InStr(," ")+1,," "))) AS lname

FROM name;




When I work on the Access database which was converted from SQL server, the
InStr DOES NOT work for it.

I know the syntax for those two may be different. I could not find info from
website: how can I find the first space and second space in name field? How can
I use IF when only one space in name field?



Thanks very much!