Solved

Convert excel formula into Access formula

Posted on 2008-06-16
19
3,652 Views
Last Modified: 2011-10-19
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
Comment
Question by:jaugermeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 2
19 Comments
 

Author Comment

by:jaugermeister
ID: 21792325
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

by:jaugermeister
ID: 21794550
anyone?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 21801452
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jaugermeister
ID: 21801762
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

by:Kelvin Sparks
ID: 21801796
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

by:jaugermeister
ID: 21801963
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

by:hnasr
ID: 21803535
Try this model, adjust to your data.

splitName.mdb
0
 

Author Comment

by:jaugermeister
ID: 21812583
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

by:jaugermeister
ID: 21813546
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

by:hnasr
ID: 21817861
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

by:hnasr
ID: 21817981
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

by:jaugermeister
ID: 21822908
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

by:hnasr
ID: 21824375
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

by:jaugermeister
ID: 21824624
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

by:hnasr
ID: 21824795
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

by:jaugermeister
ID: 21824967
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

by:
hnasr earned 500 total points
ID: 21835626
jaugermeister,

Try this model!

splitName-3.mdb
0
 

Author Closing Comment

by:jaugermeister
ID: 31467543
!!!!!!!thanks!!!!!!!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21847779
Welcome!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

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

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

Join & Ask a Question