[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Convert excel formula into Access formula

Posted on 2008-06-16
19
Medium Priority
?
3,656 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 31

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 31

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 31

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 31

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 31

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 31

Accepted Solution

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

Try this model!

splitName-3.mdb
0
 

Author Closing Comment

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

Expert Comment

by:hnasr
ID: 21847779
Welcome!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

649 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