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
1782 Views

Similar Articles

[PageSpeed] 14

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:

relocate field position
Hi, Is it possible to relocate a field position with onLoad script? For ex, based on some condition I want to move name field from first tab to second tab. I personally havent seen this, possible workaround would be to create two fields, hide one and show the other dynamically, also when this change happens you could give the share the value i.e tab 1 tab2 name1 name2 and create some code that does the following name1.hide name2.show name2.value = name1.value or visa versa? good luck, and tell us ...

Including double-quotes in strings
I want to use the substitute function to replace commas "," with double-quotes. Is there a way to specify double quotes in strings? Hi! =SUBSTITUTE(A1,",","""") Will replace all commas with " Biff "Hall" <hall@garp.org> wrote in message news:OhjSQk6oFHA.3304@tk2msftngp13.phx.gbl... >I want to use the substitute function to replace commas "," with > double-quotes. > > Is there a way to specify double quotes in strings? > > yeah. how about this. keep your data in a database and then when you ne...

How to create a single line separated by commas from a matrix?
Hello, I've the following problem. I've a table made of several columns. Each colum represents a characteristic regarding to a subject (e.g. Name, Surname, Home Street, etc...). In the rows I put the different people. I need to export these info to a format compatible with other program - an email program-, which requires the information to be arranged in a different way. The program needs all these data to be ordered in a single row, with each characteristic between quotation marks, and separated by commas, following this pattern: "characteristic 1","characteristic ...

Determining the Integer associated with a String
Can anyone point me to a source to get the Integer to String relationship in GP 9.0? Today I'm looking for RMA status. Thanks, Carolyn Thanks but nevermind, I found it in additional field information. "cbushnell" wrote: > Can anyone point me to a source to get the Integer to String relationship in > GP 9.0? > Today I'm looking for RMA status. > > Thanks, > Carolyn ...

Separate inboxes for separate accounts
How can I separate incoming e-mail from separate e-mail accounts into folders so I can tell where incoming mail came from? Using Outlook 2000. >-----Original Message----- >How can I separate incoming e-mail from separate e-mail >accounts into folders so I can tell where incoming mail >came from? Using Outlook 2000. >. > I'm a novice but I just ask that question and dl@spoofmail.com gave me the following advice: It is done with rules. 1st create your folders (sounds like you've done that.) Then select Tools / rules & alerts / New Rule... This brings up...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

Formula to find average of field for all rows that contain another field #2
Say I have a worksheet with the following information: Name Position Salary John Producer $10,000 Jeremy Producer $98,000 Jaime Producer $50,000 Darren Artist $67,000 Chris Artist $75,000 Clint Artist $30,000 Adam Artist $57,000 In Cell C2 (salary for John) I want to create a formula that looks for all rows that contain the same position as in B2 (John's position which is producer) and then calculates the average of all the salaries in column C of those rows that have the position producer. Then I want to c...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

Summing text fields?
How do I frame a query that allows me to sum up the dollars spent with a particular vendor or PO without repeating the field over and over? SELECT [PO Number], Sum([Total]) FROM YourTable GROUP BY [PO Number] ORDER BY [PO Number] ; SELECT [Vendor Name], Sum([Total]) FROM YourTable GROUP BY [Vendor Name] ORDER BY [Vendor Name] ; SELECT [Vendor Name], [PO Number], Sum([Total]) FROM YourTable GROUP BY [Vendor Name], [PO Number] ORDER BY [Vendor Name], [PO Number] ; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "DashOneChar...

where to find calculated fields
Hello, I am a CRM newbie. I did a little SQL development a long long time ago in a galaxy far far away. I am very rusty. I am trying to figure out how calculated fields are handled so that I can copy the process. I am looking at Customize Entities, Oppurtunity, Form and Views and the Attributes. I am trying to figure out how the attribute Revenue (isrevenuesystemcalculated) is calculated. I don't see anything on the forms or the attributes. Is the value calculated in the SQL databas somewhere? I would appreciate it if someone could point me in the right direction. you have a few o...

save text field w/ leading zeros in .csv format & not lose zeros?
I currently have a csv file with a field that has one digit numbers. I need to create a text field and convert the one digit number to two digit text by adding a leading zero. I have tried using the concatenate function to add the leading zero, but when I close the csv file, it reverts back to one digit. How can I get the file to save the field as text and include the leading zero? Check your CSV file in Notepad, not in Excel. Excel will frequently convert your text representation of a number into a number, even though it likely was written out to disk with the leading zero "...

Merging photo fields in Publisher
Thanks in advance. I am a teacher, trying to create a monthly "Student of the Month" publication that includes student photos. The info is all in my Access Database, but I need to put out a monthly publication with pictures of a student from each class (about 26 photos) who earned the "Student of the Month" designation. I am using Publisher to do a catalog merge with my Access database. One of the fields I really need is a photo field. I have tried to follow the directions about putting links in for the photos, but no matter what I do, the actual pictures do not s...

Address State Field
I have a client that has a need to make the address state field a drop down so that they can run reports off of it. Is there any way to do this? The only way I thought of was by creating a new field with a drop down. Any ideas would be appreciated..... Thanks! -- Tara Sifuentes, CCDA, CCNA Hi, We always create a new drop down field for Stateorprovince and we write a script in "onchange" on the properties --> Event of this new field. The code update the original field and you don't need to change all of your reports. the only thing is you need to keep the original f...

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 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? > >Thank...

Saving Calculation Fields Value
hi.. im having a hard time making a database system using only full access.. im not allowed to use any VBA codes.. now im having difficulty on calculation fields in a form.. it is said that calculation fields are not stored in a table.. my question is how can i get the value of that field if ever i have to create another form where i need its existing value.. it would be if easy i can find it in a table but like i said it only exist in the form. please help me with this.. thanks.... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/2007...

Launch Oulook Express Newsreader separately
Hi I use Outlook Express as my newsreader. My problem is that in order to access it, I need to first launch Outlook. Is there some way I can launch the newsreader portion of OE without launching Outlook? A command line switch or something I imagine. I'm using Outlook XP with OE 6 on Windows XP pro. Thanks I believe the command-line switch is /newsonly. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "aJax&...

Required field with deleted data
Hi, I have a one field continuous form that gets its data from a one-field table. The field is unique and required. The form also has a command “Close” button. I really have problems with the “On Event” order of things. If a person goes to a new record and starts to enter data and then deletes it by using the backspace or delete key and then leaves the record by 1) clicking on another record, 2) clicking on the “Close” button or 3) presses the “Enter” key. I get the Access error message because the field is required. If the new record is blank, I want to prevent the error message an...

how do I convert text string into a cell reference
I have a spreadsheet with multiple pages (a summary sheet, plus multiple single sheets with common format data for different products). In the summary sheet, I want to keep the cell reference the same, but change the page reference according to the column that the data is in. That way I can change a cell at the top of the column to pull up the right data. I can create the cell reference OK in text form using Concatenate , but cannot see how to convert the resultant text string to get back to the real data. Any ideas ?? You can use the Indirect function to return a reference. For exampl...

separate pages rather than spreads?
Hi Using 2007 I created a .pdf of an A5 booklet with 80 double sided pages and sent it to an outside printer. They replied asking "Would it be possible to send in the inside pages as separate pages rather than spreads? That is a pdf with the pages A5 size in number order 1- whatever." Anyone know how to do that please ?? -- Martin ���� Martin ���� @nohere.net wrote: > Hi > Using 2007 > I created a .pdf of an A5 booklet with 80 double sided pages and sent > it to an outside printer. They replied asking > > "Would it be possible to se...

Modified By and Modified On fields
I am trying to customize a phone form (will need to do the same to most others) by adding Modified By and Modified On fields. After publishing the form, the fields appear on the form just fine but the lookup to a user list is greyed out. Any ideas? Am I doing something wrong? I found the answer myself. Those are system-generated values and. therefore, cannot be modified by a user "mkatsev" wrote: > I am trying to customize a phone form (will need to do the same to most > others) by adding Modified By and Modified On fields. After publishing the > form, the fields ap...

Adding namespace to an XML string.
If I have a well-formed XML document in a string, what can I do to get a namespace added to it? What class, properties, and methods can I use? Thanks, Don Don wrote: > If I have a well-formed XML document in a string, what can I do to get a > namespace added to it? What class, properties, and methods can I use? The simplest and the most effective way is to crate custom XmlReader which exposes required namespace. Here is the simplest form: public class NamespacedXmlReader : XmlTextReader { public NamespacedXmlReader(TextReader r) : base(r) {} public ove...

How to set an string element's value to a CDATA block?
I've got a class hierarchy generated from a .xsd schema file using the XSD.EXE tool. One of the elements will have its "inner text" set to a CDATA block. The XSD.EXE tool exposed a "Value" property on that element to allow setting that inner text. After I create the object hierarchy and specify all the appropriate elements, attributes, etc., I serialize the objects to a .xml file using XmlSerializer. If I simply specify the "element.Value" property to the string data (what should go inside the CDATA), the string simply becomes the inner text - it's ...

Synching 2 iPhones to Outlook on one PC for separate calendaring
I want to use Outlook on our new PC to synch calendars for two iPhones belonging to my wife and myself. How do I go about this to keep the calendaring info separate; how does the iPhone know which calendar to synch with? Do I have to open Outlook to the right calendar somehow before I synch with the appropriate iPhone? Or is there some automated way of doing this so that I can just synch my iPhone without opening Outlook, and the system will know which iPhone gets synched with which calendar? Thanks -- Bob You'll need to configure the sync to use the correct profile and outlook w...

Deleting part of a string
I have a field in a report that is a comma delimited list. What I'd like is a function that will delete all after the first comma, so that I get the first value only. For example "Fred, Barney, Wilma" becomes "Fred". In other words, I'd like whatever the VBA equivalent is of: s/,.*// If "Replace" is the fxn I need, I'm not sure how to make it do this. Thanks in advance, John On Thu, 31 Dec 2009 16:34:05 -0800 (PST), John Harrington wrote: > I have a field in a report that is a comma delimited list. > > What...

insert memo feild into a memo field truncates at 255 characters -
Greetings I have a table that has a memo field to retain specific information - text - which is the base of the combo box. Via a combobox on a form this memo field is added to a forms field which is also sized as a memo called "Project_Notes" - this part works. At a certain time the user uploads this form and its feilds to a parent table which also has a memo field to recieve the data from the other memo field. 'we have created the new entry now we should update the notes strOtherFields = ",Action_By,To_Do_date" _ ...