Separating strings in a field to separate fields

Hi,

I have a field that consists of a string value that looks like Item1,
Item2, Item3, each item is separated by commas.  I want to separate
that string value so that each item has its own field.  Is there code
that will easily do this?

Thanks,
Jaime
0
jseger22
12/5/2007 8:08:57 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1867 Views

Similar Articles

[PageSpeed] 37

On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jseger22@yahoo.com wrote:

>Hi,
>
>I have a field that consists of a string value that looks like Item1,
>Item2, Item3, each item is separated by commas.  I want to separate
>that string value so that each item has its own field.  Is there code
>that will easily do this?
>
>Thanks,
>Jaime

Yes... but it differs depending on the circumstances. Are there always exactly
three items? An arbitrary number of items? Is there always at least one comma
or might some records have none? 

Take a look at the VBA help for InStr, Mid, and Split and post back with a bit
more detail (and perhaps an example) if you need more help.

             John W. Vinson [MVP]
0
John
12/5/2007 11:33:04 PM
On Dec 5, 6:33 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Wed, 5 Dec 2007 12:08:57 -0800 (PST), jsege...@yahoo.com wrote:
> >Hi,
>
> >I have a field that consists of a string value that looks like Item1,
> >Item2, Item3, each item is separated by commas.  I want to separate
> >that string value so that each item has its own field.  Is there code
> >that will easily do this?
>
> >Thanks,
> >Jaime
>
> Yes... but it differs depending on the circumstances. Are there always exactly
> three items? An arbitrary number of items? Is there always at least one comma
> or might some records have none?
>
> Take a look at the VBA help for InStr, Mid, and Split and post back with a bit
> more detail (and perhaps an example) if you need more help.
>
>              John W. Vinson [MVP]

There will always be a max of three items and they will always but
separated by commas.  I was trying to do this with the Split function
but I am only a beginner with VBA so I am having some troubles.  If
you could help me out with an example that would be much appreciated.
Thanks!
0
jseger22
12/6/2007 1:13:09 PM
Reply:

Similar Artilces:

Lookup from a range on separate sheets
Hi, I am trying to look up values from the same fixed range (c50:E200) in 45 sheets and extract the values onto one sheet (same workbook) 3 columns at a time (ie first sheet B-D, second sheet E-G etc). I can't work out how to put the sheet name (or a reference to the sheet number) in a formula in the destination sheet - is it possible or do I have to use a macro? I can write the code for a macro, but as it is statistical process control limits which are added to each month, it means rerunning the macro every time Any help much appreciated! Paul Paul, In the sheet/cells that ...

Using a formula to fill a field
I am ery new to Access and was wondering, is there anyway to make a field fill automaticaly with a formula? Like in excel for example if you have a fromula on a cell and the arguments change the cell changes. I have a table with a field of invoice amounts and then a table with account info, i want to know if there is a way for the account table (ie: Balance field) to update automaticaly when you add an invoice with that accountID. Access is a relational database. An Access table may look like a spreadsheet, but it is really just a "bucket o' data". No, you can't ad...

Connection String
Hi... Our company just purchased some propriety software that is on a server running MSSql. I want to connect to it to retrieve data only. I can connect using ODBC through Access 2000, but each time I try to use the tables, I have to supply the password for the program. I want to use VB6.0 to connect to the database, but can't figure out the connection string. An example provided in VB6.0 is: wrkODBC.OpenConnection("Connection1", , , _ "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=publishers") (what reference do I need in the Project to be sure what I ...

Hidden fields in new emails
When I begin to type a new message, the text doesn't appear (the words are hidden) until spell check/sending/or printing. When I select the icon for the font color of text (black) it begins to show my typing as I'm composing the message, but the other words (anything I typed in the 'hidden field') only shows up in the spell check portion and remains hidden. Why are my messages starting with this hidden field and how do I get rid of it? I'm sure I must have pressed some command inadvertently as it was not always working like this. (only been using about a week so s...

Auto Filling Fields in Current Record from Previous Record
Hello, I have looked through posts related to Auto-Filling a field from a previous record but am still a little unsure of how to do things. What I would like to do is auto fill a field in the current record with one from a different field in a previous record. What I have is a form that records "engine hours" and "tonnes moved" for a piece of equipment. The operator will record the staring hours and tonnes (at the start of a day) and the hours and tonnes at the end of the day. So in the database, I would like the "Starting Hours" and "Starting Tonnes"...

Sorting Issue (Mixde Fields)
I have an issue where I need to sort a worksheet based on a field (Bin Location)that is sorting as seen below... 1A1 1A20 1A21 1A22 1A3 1A4 How can I get Excel to sort it the following way... 1A1 1A3 1A4 1A20 1A21 1A22 Please help.... I need to get this figured out today. I appreciate any responses. Thanks, Jonathan G. One way: add a column showing the length of the column you want to sort, then sort using the new column then the "real" column. "Jonathan G." wrote: > I have an issue where I need to sort a worksheet based on a field (Bin > Location)that is s...

Outlook 2003 cannot see other email addresses in the to field
Outlook 2003 cannot see other email addresses in the to field - when someone emails me and a group of other people, I only see my email address. I cannot reply to all and send the other people an email. Is this a bug? No, they probably used the BCC field or a mail merge instead so only your address shows up. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Save Embedded Pictures in Their Original Format -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Jenni&...

How do I create drop down form fields in Excel?
Is it possible to create dropdown lists in Excel? If so, how do I do it? Thanks You can use Data Validation to create the dropdown lists. There are instructions in Excel's Help, and here: http://www.contextures.com/xlDataVal01.html Micki wrote: > Is it possible to create dropdown lists in Excel? If so, how do I do it? > > Thanks -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Adding company name to the "From" field
Hopefully this is an easy problem. We are running Exchange 2003 on Windows Server 2003 in a mixed domain. (Win2k and Win2003 servers) Lets say I am working for a company named "XYZ". The owner of the company wants the "from" line in the email client to say "Bill Smith, XYZ Company". Unfortunately I can not find a way to do this. Any help is appreciated. Bob Hi Bob, Just use ADModify to rewrite the Display name for every user in your company, or for users of that company (if companys are sharing and AD) ADModify can be found here: http://www.gotdotnet.co...

edit suggest names when completing To, cc and bcc fields
Apparently I don't have an Address Book linked to Outlook 2000. No matter, because I seem to be ok with the suggest names feature, which I guess is not Address Book-linked, but linked to a memory bank of names and corresponding addresses typed in during the past. However, here is my question: How do you edit this Suggest Names list? In particular, A. Suppose you want to stop Outlook from suggesting a particular name because it is not current anymore. Worse, you might inadvertantly click on it and send msg to somebody you don't want to receive it. Is there a way to delet...

Searching in a column of strings
Hi I try to find how many cells have a sequence of charcters. From Cell A1:A50. Exemple for "NY" : cellA1 = NY123 cellA2 = NY3235 cellA3 = NY5434 cellA4 = LA4234 function(NY) = 3 Like Seach but instead to be in a single cell, it's in a sequence of cell? Thanks Jack Jack =COUNTIF($A:$A,"*NY*") Regards Trevor "Jack" <anonymous@discussions.microsoft.com> wrote in message news:348501c3fd71$700c6c60$a001280a@phx.gbl... > > Hi > > I try to find how many cells have a sequence of > charcters. From Cell A1:A50. > > Exe...

Problem displaying string data in Edit box
Hi, I am trying to display a CString in an Editbox. In the dialog, I have set the edit box to multiline, read-only, auto vertical scroll and want return. I set the Edit box with the CString variable when a serial handler occurs. The serial handler code is obtained from http://www.codeproject.com/KB/system/serial.aspx and have integrated into my main dialog. I have created a edit box variable in my dialog and create a member variable within the main dialog window. When the serial handler is called, I store the serial message into the CString variable and use SetwindowText() to display the st...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

value from field not accepted within onlineform (3.0 -> 4.0)
Hy, We just tried to update our running CRM 3.0 to 4.0 there where nearly no problems! (1 report was not upgradeable) We where very happy. but right now a real problem was discovered: the fields: longitude and latidude from the contact form are no more accepted. Enter a value beetween .... But the values do perfectly match within the range When I delete the values I can save the form. When there is any value in the fields it causes the error: Enter a value ... Best regards for any help ...

Separate Raster and Vector Resolutions in Print Driver
Hello, I am developing a monolithic print driver from the ground up. I have succeeded in establishing communications between my driver and the GDI (i.e., my DrvXXX functions are being called). When my DrvEnableSurface function is called, I am creating a surface bitmap with a call to EngCreateBitmap. The call to EngCreateBitmap only asks me for the height and width in pixels, but it does not ask me for the resolution (i.e., 250 dpi, 500 dpi, 1000 dpi, etc.). I am assuming that when you call EngAssosiateSurface that the bitmap which was created is tied to the resolution of t...

Related Fields in sub activities view
CRM 4.0 In the standard My Activities view I am able to display information from the Account (Regarding) i.e. industry, city etc. When switching to Phone Call or Appointment, the information is not displayed even though the facility is there in admin to add these fields - is there a fix for this? ...

Vendor Lookup Field with Modifier
I would like to modify the ap entry form and create 1 or 2mores field. This additional field will be used to track related party vendor number/name For example, the invoice is being paid to Vendor A. But because our invoice for Vendor A may be related to Vendor B, I would like to enter this info in the system. Currently, we just enter the related vendor ID in the note field. But due to the large number of related party transactions, the users are entering incorrect vendor numbers or they are not properly formatted , in the note field. This makes it difficult to analyze related party...

Parse field in query
I have a table called MasterData with a field called Originator. The field data looks like this... Lou Santana Steven Sentry/Miami/PU/WC/US CN=Kathy T. Buon/OU=Miami/OU=BU/O=WC/C=US Terry J Leif I need it to look like this... Lou Santana Steven Sentry Kathy T. Buon Terry J Leif How can this be parsed?? -- deb What a mess that you're stuck with! Below will look for the first / and return the string before it in a query. If there isn't a / it returns the entire string. Of course this doesn't help with the CN= in CN=Kathy T. Buon. Parsed: IIf(InStr([Originator],"/")...

Parse Name Field into First Name Last Name
I have a field called [Patient Name] that I want to parse out into a First Name Field and Last Name Field. I am using the Charindex() to find the comma that seperates the First and Last Name, I am then passing the Charindex value to the Left Function to grab the Last Name. Below is what the code looks like: LEFT([Patient Name],Charindex(',', [Patient Name])-1) AS LAST_NAME I am getting the below error when using the above code Invalid length parameter passed to the LEFT or SUBSTRING function. Warning: Null value is eliminated by an aggregate or other SET operation. ...

Return only the numbers contained in a field
Hello, I would like some help please building a query that will return the numbers only contained in a field: Table "PurchaseOrder" has a "Memo" field that usually has the following data: "Sales Order 7540:" or "Sales Order 34:". How can I create a query that will show me all the Sales Orders and their linked Purchase Orders (PurchaseOrder.RefNumber) where the SalesOrder.RefNumber equals only the numbers in PurchaseOrder.Memo field? Thanks in advance, Brett Welllll. You can but it might not be pretty, accurate, or fast. Actually I'd expect ugly...

Need help getting query to reference a field in a subform
I am trying to run a query using a specific field in a subform as the criteria. The query is displaying one combo box from the Form, as well as several fields from its own subform. My Form name is: FormGrantManagement My Subform name is: TableMOA subform My Subform's subform is: TableCostReports The field in the subform I want to reference is: MOAID I have found other help topics that lead me to think that a criteria along the lines of [Forms]![FormGrantManagement]![TableMOA subform]! [Form]![MOAID] is what I need to use, and I have tried multiple variations of this but witho...

How can I print documents saved separately in My Pictures back-to-
Need to print scanned docs saved in My Pictures back-to-back to save paper. How? I know about Print-properties-Duplex, but these are all separate files, so not sure how to do it. Help! To use Word to do it, you would have to insert all of the scanned documents into a Word document and then print that document in duplex mode. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "anno" <anno@discussions.microsoft.com>...

Serializing object to XmlDocument (or string)
I am on .NET 2.0 and am trying to serialize an object to an XmlDocument (or a string which I can then create an XmlDocument from). What I have so far is (User is my class); XmlSerializer x = new XmlSerializer(typeof(User)); MemoryStream memStream = new MemoryStream(); How can I get this to a string or an XmlDocument? The serialization works fine as if I serialize to a text file (using StreamWriter), everything looks fine. I've try converting to bytes, reading on XmlWriter, but nothing seems to work. Thanks, ...

drop page fields here and drop series fields here
I have an OLAP chart ion my chart sheet. On the bottom and top have drop page fields and seriers fields here. I have no idea which fields I can drag and drop. any information is great appreciated, Hi, This chart is a pivotchart? If so the field list should be displayed. You can drag any field in the field list to any area, so your choice should be based on what you want to display and how you want to display it. Note that as you change the pivot chart the pivot table will also change and visa versa, so you can drag the fields around on the pivot table to see the effects on the pivo...