Solved

Convert excel formula into Access formula

Posted on 2008-06-16
19
3,645 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
  • 10
  • 7
  • 2
19 Comments
 

Author Comment

by:jaugermeister
Comment Utility
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
Comment Utility
anyone?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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

by:jaugermeister
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Try this model, adjust to your data.

splitName.mdb
0
 

Author Comment

by:jaugermeister
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
jaugermeister,

Try this model!

splitName-3.mdb
0
 

Author Closing Comment

by:jaugermeister
Comment Utility
!!!!!!!thanks!!!!!!!
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Welcome!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now