splitting a field into two fields; one in ()

I have a Name field in my database that is LastName,FirstName(dept).  I have 
figured out how to split last name and first name but I don't know how to 
leave out the department name that is in () after the first name.  Any 
suggestions?

0
Utf
12/15/2009 8:35:02 PM
access 16762 articles. 2 followers. Follow

2 Replies
832 Views

Similar Articles

[PageSpeed] 18

Quick and Dirty way to get the field down to just LastName,Firstname
is to use the following as an expression in a query:

LastFirst:Left([Name],Instr([Name],"(")-1)

This should give you all of the characters to the left of the opening
parenthesis. You could then apply your method for splitting the First
and Last Names.


HTH
0
techrat
12/15/2009 9:02:44 PM
For the Last Name;

Left([YourField],InStr([YourField],",")-1)

For the First Name without the dept;

Mid([YourField],InStr([YourField],",")+1,InStr([YourField],"(")-(InStr([YourField],",")+1))

BTW - hopefully your field is not actually called Name, as this is a
reserved word in Access and should not be used as a field or
control name.

-- 
_________

Sean Bailey


"LisaK" wrote:

> I have a Name field in my database that is LastName,FirstName(dept).  I have 
> figured out how to split last name and first name but I don't know how to 
> leave out the department name that is in () after the first name.  Any 
> suggestions?
> 
0
Utf
12/15/2009 9:38:01 PM
Reply:

Similar Artilces:

Split tasks a splitting headache
Inherited another Project IMS. I am noticing right off that the previous scheduler has been starting tasks without their FS preds completing. Yeah, it does show up as a spit bar in the gantt chart. But, does anyone know a way to filter out just task that started before predecessor completion? I'm dealing with several thousand lines. Thanks. -- trailerpup ------------------------------------------------------------------------ trailerpup's Profile: http://forums.techarena.in/members/116596.htm View this thread: http://forums.techarena.in/microsoft-project/1290853.htm ...

How to randomly split a whole dataset into two sub-dataset?
Hi, At your possible convenience, might anyone please kindly answer my question? Thank you very much. How to "RANDOMLY" split the whole data set (n=2000) into two sub dataset (n=1000; n=1000) in SPSS or Excel? Thank you very much. Please take care Caroline zencaroline <zencaroline@gmail.com> writes: > How to "RANDOMLY" split the whole data set (n=2000) into two sub > dataset (n=1000; n=1000) in SPSS or Excel? Create a new variable whose value is randomly distributed. Sort the data on this variable. Take the first 1000 cases ...

Date form field in Word
I've created a form using the drop-down date field. However, after saving and restricting editing on the document, the form works when tabbing TO the date field but after selecting a date, the "form" will not tab to the next field, which happens to be a fill-in field: it will only tab to the next date field. HELP! If the date fields are Date Picker content controls, what you are seeing is to be expected. You cannot Tab from a content control to a field in Word. -- Stefan Blom Microsoft Word MVP "April" <April@discussions.microsoft.com> wrote...

Form with two nested subforms.
I am setting up a database with three main forms. The parent form is called [Orders]. The first subform is called [People]. the Second subform is called [records]. Each order can have multiple people in it. Each person can have multiple records. To keep all of the data on one page, I nested two different subforms. The problem is that I am having problems running simple functions on my second subform [records]. The first problem is using the findrecord operation to find a value (MaxID) in the ID field. Here is what I have: Forms![Orders]![People].Form![Records].SetFocus Forms![Or...

Unwanted replicated split screen
I know I could re-install excell but before I do, is there anyone here who can help me disable a setting that I have no idea how it came about. I can only describe it as 2 identical execl grids on the same worksheet. Accompanying image will show you. Help. Thanks. +-------------------------------------------------------------------+ |Filename: excel_display_duplication.GIF | |Download: http://www.excelforum.com/attachment.php?postid=3855 | +-------------------------------------------------------------------+ -- BoyLeroy -----------------------------------...

Can't find my Front End Folder after database split
I just split my database base to allow for ease of use for multiple users. Ererything, it seemed, went smoothly until i try to find the fron-end folder. It is nowhere to be found. Should it be label ****_fe.mbd like the back end? Please help. Thaanks. It will be called that, unless you typed something else in the save as dialog. I thought it ended up in the same folder as the backend after splitting. Easiest way is to go back to the original and split it again, this time take note of where you save it before you press the button. Jeanette Cunningham "Joe" <Joe@discussi...

Display Data On Split Form
Not sure how to ask this but here goes. In AC 2007 I have a split form that I want to display a value that will show regardless of the row I select in the datasheet. This is the number of vacation days one will have through-out the year and of course it changes as vacations days get used up. Any help will be appreciated, James "JamesJ" <jjy@darwin_roadrunner.com> wrote in message news:6A4DFFD9-3302-4A10-B51A-153373D266FD@microsoft.com... > Not sure how to ask this but here goes. > In AC 2007 I have a split form that I want to display a value > that will show regardl...

Split data into new sheets
I have a (very) long list, sorted by account code. I would like to write a macro that splits the list into separate sheets in the workbook, with a separate sheet for each account code. Ideally, I would also like to rename each sheet to show which account code the sheet contains. I have no idea, though, where to start. Any ideas? Thanks in advance. Hi bernard How many different accounts are in the list (more or less than 250)??? below some code that I use to split files by account numbers where the user has to select a cell within the column that contains the account number... Hope thi...

How can I separate items from one cell into two?
-- bootsy here's two ideas 1) use data / text to columns 2) use a formula such as =LEFT(A1,3) or =RIGHT(A1,4) if you'ld like more assistance please post back with additional details and type out two or three examples of your data. Cheers JulieD "Bootsy" <Bootsy@discussions.microsoft.com> wrote in message news:14CF8AC7-AF0E-47E3-8B04-E3C95A9ADEF5@microsoft.com... > > -- > bootsy Peo Sjoblom wrote: <NOTHING>! http://dts-l.org/goodpost.htm http://www.netmeister.org/news/learn2quote.html -- Interim Systems and Management Accounting Gordo...

Changing mulitple fields visibility from a single data entry
I want to use one control to determine which others within my form are visible i.e. In a text box labelled 'How Many' if 1 is entered then the text box labelled 'one' is visible and box's labelled two, three and four are invisible, if 2 is entered then 'one' and two' are visible and so on. Any help would be appreciated. Thanks You've described a "how" ... how you are trying to do something. If you'll describe the "why" and "what" (what will having this allow you to accomplish?), the newsgroup readers may be able to...

split column
I have a column of data in the following format column A John B. Smith What I want to do is split this column into three separate column so that it will appear as below Column B Column C Column D John B. Smith. Is there a formula that can help me do this. TIA Tlee, If all the names follow the same format (you don't say), you can use Data - Text to columns, using a space as the separator. Make sure that the columns to the right are empty before you start. -- Earl Kiosterud mvpearl omitthisword at verizon period net ---------------------------...

Populating a field in a record
I have a form with a supplier and customer, then i have a subform within the form which lists the products that the customer wishes to order. In the subform i have four fields. Stock Reference Product, quantity and price. What i would like to do is choose the product and then double click on the stock reference, which brings up a list of all current available stock. I then can click on the stock i wish to take from and then the reference number will populate the stock reference field in the subform. The problem i have is when i have more than one product line, the value in the stock ref...

query to overcome field type problem?
I have a contacts table... let's say 2 fields- ID and email. I have a groups table... it has a number of fields that are linked back to the contacts field already (contact 1 thru nn), so that the groups table just has a contact id number and I look up their email from the other table. One field is also the "primary contact" (similarly linked)... for the person who "owns" that group. Works nicely so far, (thanks, techrat!). Until I try to send email to that primary contact to have them verify the details.... Their email id doesn't show up in the wiz...

copy date from one column to another
I want to copy data from one column to another column, but add the copied value to existing data. The field I'm copying from contains dates. The field I'm copying to contains text. For example: Before the copy: Column A Column B 3/21/2008 Lorem Ipsum dolor...etc. After copy: Column B Lorem Ipsum dolor...etc. Date in: 3/21/2008 How can I accomplish this? Thanks for any help. Regards. Sub addtocolb() For Each c In Range("a2:a4") c.Offset(, 1) = c.Offset(, 1) & _ " Date in: " & Format(c, "mm/dd/yyyy&...

save split table in word
I am in word; after I split the window, I need to save but it won't let me. How do I save the split window in word? "Split window" is something that doesn't get saved when you close your document. On Feb 3, 11:49=A0am, chellehicks <chellehi...@discussions.microsoft.com> wrote: > I am in word; after I split the window, I need to save but it won't let m= e. =A0 > How do I save the split window in word? ...

User Defined Fields
Hi There I am currently evaluating MC CRM for my organization. One important feature that I am looking for is the ability to add user defined fields to forms WITHOUT programming. Is this possible? Can someone please tell me how? I have discovered that you can customize forms, but only with fields that are already in the DB. Any help or direction is appreciated. (I am using CRM 1.2) Thanks in advance You can add new fields via the Schema Maager utility within Deployment manager. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------...

row fields in pivot tables
I was wondering if you could answer a question about formating information on Pivot Table spreadsheets in Excel. You know how one can alloacte column headings in the pivot table layout to either Page , Row, Column, or Data. Well, when you assign more than one column heading to Row it creates a hierarchy of levels on different rows. How does one get it not to do this so you can have this information on the same row? Please reply when you get a chance. Thank you. Best Wishes, Saeed I don't think you can and still keep it a pivottable. I copy the sheet and then fix that up the w...

Pub 2003 One Color Gradient Changes in PDF
I have selected a one color gradient (horizontal) from the fill effects box, with dark at top and bottom, fading into the center of the page. This is a background that fills the page behind text and a photo. It will bleed, so I need the darkest part at top and bottom. The gradient looks terrific on the screen, but when I convert the file to PDF, the gradient reverses itself - the darkest part is in the middle of the page, and it fades toward the top and bottom. I have tried saving the text box (the only thing in it is the gradient fill) as a PNG and reinserting. When I converted that ...

Add Capital One Credit Card Account
In Money Deluxe (2008) the add credit card account wizard tries to force the use of Capital One's banking web site, which is wholly different to, and uses different log-in credentials, to the Capital One Credit Card web site. How do I force the add credit card wizard to reference the credit card site? Capital One has an OFX service within Money, listed as Capital One Bank, that is available for customers that have either just bank accounts or both bank accounts and credit cards which is why this FI name is available on both the bank and credit cards list of FI choices. In other word...

Calculated a field in a subform.
I have created a subform based on a query with a field TotalDays and this is a calculated field: Dim Totaldays As Integer Dim DateIn As Date Dim DateOut As Date If DateOut Is Null Then TotalDays=DifDate ("d",[DateIn],[Date]) Else TotalDays=DifDate ("d",[DateIn],[DateOut]) End If My questions are:Where is the best place to calculated the field in the subform or in the query? If the answer is in the subform in what event i have to put the code? Thank you for your time. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acc...

string fields in smartlist to be able to searched by number
Current functionality in Greatplains 8 only allows the user to search on a string field however when the results are returned the data included irrelevant data and not that specified in the search. Example Item number search between 1000 and 2000 Results of search on item number 1000, 100086, 100098, 2000 the results should only return 1000 and 2000. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, fo...

Reverse Split
Money International Version 2003 trying to do a 0.25 for 1 split. Money wants integers in the split field, so I tried to do a 1 for 4 "split". Money gave a completely unrelated answer. I then did a 50 for 200 "split" (I have 200 shares being reduced to 50) - again it gave a number completely unrelated. I tried various permutations and each time, the division or multiplication numbers were total nonsense. Any help? In microsoft.public.money, ZootRot wrote: >Money International Version 2003 > >trying to do a 0.25 for 1 split. Money wants integers in ...

How do I remove split a split window?
I selected "View" "split" and now have a split window. How do I remove the split? "Norm" <Norm@discussions.microsoft.com> wrote in message news:33CA1631-20A1-457C-A797-F9ECE0180ED8@microsoft.com... >I selected "View" "split" and now have a split window. How do I remove the > split? Same way as you split it.... Or double click the "line". -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Gordon" wrote: > "Norm" <Norm@discussions.microsoft.com> wrote in message > news...

New Contacts Field/Folder/Section
Hi, I have Outlook 2003. I would like to ask if it's possible to only view selected contacts when creating a new email? I would like to only see the contacts I have already set up in Outlook Express. I have a bunch of contacts in the contacts section of Outlook but I would like to only see about 20 or so contacts (that I choose) when creating a new email. Maybe this sounds ridiculous but I figured I'd give it a shot. Thanks for any help Jay Create a view of your Contacts folder that shows only those 20 contacts, then when it's time to write the e-mail, switch to that vi...

grid off on one page
Is it possible to turn the grid off of only one page? thanks On Thu, 14 Feb 2008 10:37:11 -0600, "greg" <iuouh@ghfhg.com> wrote: >Is it possible to turn the grid off of only one page? Not really as it is a document wide setting.... however, there is workaround open the page shapesheet menu window -> Show shapesheet and set XGridSpacing and YGridSpacing to a large value. -- Regards, Paul Herber, Sandrila Ltd. SSADM for Visio http://www.saadm.sandrila.co.uk/ On Thu, 14 Feb 2008 16:47:53 +0000, Paul Herber <SubstituteMyFirstNameHere@pherber.com> wro...