Help with Module

[Guest]

I am trying to import data from a text type file into a table. Problem is,
the data involves names, some with middle initials, some without.

I need the data to be without initials....so, I have the following line to
add the data:

tblEBIZHours![TECHNAME] = Left(tblEBIZTemp![Emp Nm], InStr(1,
tblEBIZTemp![Emp Nm], ",") - 1) & ", " & Trim(Mid(tblEBIZTemp![Emp Nm],
InStr(1, tblEBIZTemp![Emp Nm], " ") + 1, InStr(InStr(1, tblEBIZTemp![Emp Nm],
" ") + 1, tblEBIZTemp![Emp Nm], " ") - InStr(1, tblEBIZTemp![Emp Nm], " ")))

Which works fine if there is a middle initial, however, I error out if the
code doesn't find anything with a middle initial. I tried an If...Then
using IsNull, that didn't work either.

Any suggestions?

I'm using Access 2003

 

[Ken Snell \(MVP\)]

Show us some sample data with and without the initials and identify the
pieces of the data that you want to keep.

 

[Guest]

The text file carries in "SMITH, JOHN P" or "DOE, JANE"

I need only the last name and first name.

Show us some sample data with and without the initials and identify the
pieces of the data that you want to keep.

--

Ken Snell
<MS ACCESS MVP>

I am trying to import data from a text type file into a table. Problem is,
the data involves names, some with middle initials, some without.

I need the data to be without initials....so, I have the following line to
add the data:

tblEBIZHours![TECHNAME] = Left(tblEBIZTemp![Emp Nm], InStr(1,
tblEBIZTemp![Emp Nm], ",") - 1) & ", " & Trim(Mid(tblEBIZTemp![Emp Nm],
InStr(1, tblEBIZTemp![Emp Nm], " ") + 1, InStr(InStr(1, tblEBIZTemp![Emp
Nm],
" ") + 1, tblEBIZTemp![Emp Nm], " ") - InStr(1, tblEBIZTemp![Emp Nm], "
")))

Which works fine if there is a middle initial, however, I error out if the
code doesn't find anything with a middle initial. I tried an If...Then
using IsNull, that didn't work either.

Any suggestions?

I'm using Access 2003

 

[Ken Snell \(MVP\)]

Try this:

tblEBIZHours![TECHNAME] = IIf(InStr(InStr(tblEBIZTemp![Emp Nm], ", ") + 2,
tblEBIZTemp![Emp Nm], " ") <> 0, Left(tblEBIZTemp![Emp Nm],
InStr(InStr(tblEBIZTemp![Emp Nm], ", ") + 2, tblEBIZTemp![Emp Nm], " ") -
1), tblEBIZTemp![Emp Nm])
--

Ken Snell
<MS ACCESS MVP>

The text file carries in "SMITH, JOHN P" or "DOE, JANE"

I need only the last name and first name.

Show us some sample data with and without the initials and identify the
pieces of the data that you want to keep.

--

Ken Snell
<MS ACCESS MVP>

I am trying to import data from a text type file into a table. Problem
is,
the data involves names, some with middle initials, some without.

I need the data to be without initials....so, I have the following line
to
add the data:

tblEBIZHours![TECHNAME] = Left(tblEBIZTemp![Emp Nm], InStr(1,
tblEBIZTemp![Emp Nm], ",") - 1) & ", " & Trim(Mid(tblEBIZTemp![Emp Nm],
InStr(1, tblEBIZTemp![Emp Nm], " ") + 1, InStr(InStr(1,
tblEBIZTemp![Emp
Nm],
" ") + 1, tblEBIZTemp![Emp Nm], " ") - InStr(1, tblEBIZTemp![Emp Nm], "
")))

Which works fine if there is a middle initial, however, I error out if
the
code doesn't find anything with a middle initial. I tried an If...Then
using IsNull, that didn't work either.

Any suggestions?

I'm using Access 2003