Convert excel formula into Access formula

I'm trying to run a formula in access that I have been using for excell. I'm new to the world of Access so I don't know how to do name extraction in Access. I have the following formulas in excell that I used to extract first middle last suffix from a single cell that I now want to use in access to run a query.

MIDDLE INITIAL:  
=IF(ISERROR(TRIM(MID(A2,FIND(" ",A2,FIND(",",A2)+2),FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)-FIND(" ",A2,FIND(",",A2)+2)))),IF(ISERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2))),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2))),TRIM(MID(A2,FIND(" ",A2,FIND(",",A2)+2),FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)-FIND(" ",A2,FIND(",",A2)+2))))

SUFFIX:
=IF(ISERROR(RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1))),"",RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,FIND(",",A2)+2)+1)))

LAST NAME:
=if(a2="","",LEFT(A2,(SEARCH(",",A2))-1))

FIRST NAME:
=TRIM(MID(A2,FIND(",",A2)+1,(FIND(" ",A2,FIND(",",A2)+2)-FIND(",",A2))))
jaugermeisterAsked:
Who is Participating?
 
hnasrConnect With a Mentor Commented:
jaugermeister,

Try this model!

splitName-3.mdb
0
 
jaugermeisterAuthor Commented:
BTW the field that contains the Names in Access is called PATIENT and the new fields that I want to create are First Name, Middle Initial, Last Name, Suffix

So this is roughly what I want to do:

PATIENT                     FIRST NAME           MIDDLE INITIAL             LAST NAME             SUFFIX
DOE,JOHN C JR         JOHN                         C                                   DOE                          JR
0
 
jaugermeisterAuthor Commented:
anyone?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kelvin SparksCommented:
MiddleInitial = Mid([Patient],Instr(," ",[Patient])+1,1)

FirstName = Mid(Patient,Instr(1,",",Patient)+1,Instr(1," ",Patient)-Instr(1,",",Patient)+1)
LastName = Left(Patient,Instr(1,",",Patient)-1)

Suffix = RIGHT(Patient,Len(Patient)-InstrRev(Patient," "))

0
 
jaugermeisterAuthor Commented:
When I place that in the field I get a syntex error? can I not just drop that formula in the first field?
0
 
Kelvin SparksCommented:
No, you need to call some code to receive say a field with the full text, and then populate another unbound field with one or other.

If the field with patient is in (say named txtPatient), create an after update event to update an unbound field (txtSurname)

Me.txtSurname = Left(Me.txtPatient,Instr(1,",",Me.txtPatient)-1)
0
 
jaugermeisterAuthor Commented:
Kelvin, I just begun using Access last week (new job) so please break it down for me...

The fields I have in the query are:

Patient        Strret       City       State       Zip

The only information I'm concerned with right now is the patient field because the name gets kicked out like this:

DOE, JOHN C JR
DOE, JANE A

and I need 4 additional fields to pull the patient name field apart into:

First Name       Middle Initial           Last Name           Suffix

so when I go to fill in the new colomn there are 6 rows total:
FIELD:
TABLE:
SORT:
SHOW:
CRITERIA:
"OR"

How do I create the 4 new columns with the names split up?
0
 
hnasrCommented:
Try this model, adjust to your data.

splitName.mdb
0
 
jaugermeisterAuthor Commented:
Is there a way to do it without having the additional fields of Left1 and left2? I wouldn't mind having them there but since everytime I try to deslect them to not show in the report Access requires me to submit Parameters.
0
 
jaugermeisterAuthor Commented:
okay when I enter the fields per the example the query still asks me for additional paramiters... don't know why cause I set the additional fields to point to the patient field however it still wants me to give it a specific name to search for. what am I doing wrong?

PATIENT= 1st field Data Source

Last Name: Trim(Left([PATIENT],InStr(1,[ PATIENT],",")-1))
left1: Trim(Replace([PATIENT],",","",InStr(1,[ PATIENT],",")))
First Name: Trim(Left([Left1],InStr(1,[Left1]," ")-1))
left2: Trim(Replace([Left1],",","",InStr(1,[Left1]," ")))
Middle Initial: IIf(Len(Trim([left2]))=1,Trim([left2]),Trim(Left([Left2],InStr(1,[Left2]," ")-1)))
Suffix: IIf(Len(Trim([left2]))=1,"",Trim(Right([Left2],Len(([left2]))-1)))
0
 
hnasrCommented:
jaugermeister,

You can have another query to select the required fields excluding the Left1, ... fields
Actually I use this approach because it is easy to maintain!
You may have it as one query but microsoft suggests simplifying queries.

Anyway, since you like one query, I'll try to give it a try!
Good luck!
0
 
hnasrCommented:
Here is another version: (Query: splitName_2)

SELECT a.myText, Trim(Left([myText],InStr(1,[myText],",")-1)) AS [Second], Trim(Left(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," ")-1)) AS [First], IIf(Len(Trim(Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ))=1,Trim(Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ),Trim(Left(Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ,InStr(1,Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ," ")-1))) AS Middle, IIf(Len(Trim(Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ))=1,"",Trim(Right(Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ,Len((Trim(Replace(Trim(Replace([myText],",","",InStr(1,[myText],","))) ,",","",InStr(1,Trim(Replace([myText],",","",InStr(1,[myText],","))) ," "))) ))-1))) AS Suffix
FROM a;

splitName-2.mdb
0
 
jaugermeisterAuthor Commented:
hnasr...

works sorta... I say that because with the ever changing variety of names in this country I have to account for double middle, last names and extremely long names...

so with the formulas you gave me it works but bombs out when the name field doesn't have a middle name or a long fist middle or last name... see the attched as an example.

Below is the SQL code I'm using...

SELECT tblDefertoNetwork.PATIENT,
Trim(Left(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")-1)) AS [First],

IIf(Len(Trim(Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")))))=1,Trim(Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")))),Trim(Left(Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," "))),InStr(1,Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")))," ")-1))) AS [Middle],

 Trim(Left([PATIENT],InStr(1,[PATIENT],",")-1)) AS [Last],

 IIf(Len(Trim(Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")))))=1,"",Trim(Right(Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," "))),Len((Trim(Replace(Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],","))),",","",InStr(1,Trim(Replace([PATIENT],",","",InStr(1,[PATIENT],",")))," ")))))-1))) AS [Suffix]

FROM tblDefertoNetwork;
ACCESS-Error.bmp
0
 
hnasrCommented:
jaugermeister,

The problem becomes complicated if you want to use just one Query!
Access has Modules, Macros to help easying developers' lives.
If you are happy with code, just a function or so, we may give it a try!
0
 
jaugermeisterAuthor Commented:
okay, what ever solution I can get is fine by me. How can I be able to solve this issue with  Modules or Macros?
0
 
hnasrCommented:
ok!
So possible name fomats are (Check that applies)

1 Last,Frst M Suffix
2 Last,First M
3 Last,First Suffix
4 Last,First
0
 
jaugermeisterAuthor Commented:
Possible Name formats:

1. Last, First M Suffix    (John M Doe Jr)
2. Last, First M    (John M Doe)
3. Last, First  (John Doe)
4. Last, First First M Suffix   (Ann Marie C Smith Jr)
5. Last, First First M    (Ann Marie C Smith)
6. Last-Last, First M Suffix    (Jane M Smith-Doe Jr)
7. Last-Last, First M    (Jane M Smith-Doe)


That should be the majority of the name combos...
0
 
jaugermeisterAuthor Commented:
!!!!!!!thanks!!!!!!!
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.