Solved

# Convert excel formula into Access formula

Posted on 2008-06-16
3,645 Views
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))))
0
Question by:jaugermeister
• 10
• 7
• 2

Author Comment

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

Author Comment

anyone?
0

LVL 22

Expert Comment

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

Author Comment

When I place that in the field I get a syntex error? can I not just drop that formula in the first field?
0

LVL 22

Expert Comment

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

Author Comment

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

LVL 30

Expert Comment

Try this model, adjust to your data.

splitName.mdb
0

Author Comment

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

Author Comment

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

LVL 30

Expert Comment

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

LVL 30

Expert Comment

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

Author Comment

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

LVL 30

Expert Comment

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

Author Comment

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

LVL 30

Expert Comment

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

Author Comment

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

LVL 30

Accepted Solution

hnasr earned 500 total points
jaugermeister,

Try this model!

splitName-3.mdb
0

Author Closing Comment

!!!!!!!thanks!!!!!!!
0

LVL 30

Expert Comment

Welcome!
0

## Join & Write a Comment Already a member? Login.

QuickBooksÂ® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of IntuitÂ®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain unâ€¦
Overview: This article:       (a) explains one principle method to cross-reference invoice items in QuickbooksÂ®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a Mâ€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final pâ€¦

#### 744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!