Splitting Text Values From A String Into Separate Field
Jan 25, 2005
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
I have a db with a field that I need to split and put values into two other fields. The format of field1 is either a-b, aa-b, aa-bb, or a-bb. I want to take the value before the "-" and put it into field2 and then take the value after the "-" and put it into field3 and then delete field1. Is there a straightforward way to do this? I want to do it in the query design grid so let me know what should be in the "criteria" line versus the "update to" line.
I'm using Access 2000 and have approx 5500 records to convert.
I have a single field in a table called "Client Contact", where users enter a semicolon between the name, address, and city state & zip. My reason for this was so we could copy client info with a single copy and paste (like from an email). But, on the final report, it needs to have these three parts split up into different lines, or even different textboxes. I can't find a way to do that.
I'm trying to make a database to track inventory or several items. Basically, I have four tables:
1) RawMaterialList - includes a list of all raw materials. 2) PartList - includes a list of all finished product using said raw materials. 3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials. 4) ShipmentRecord - contains details of daily shipments.
Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.
Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:
1) Item - a list of of raw materials from table RawMaterialList 2) Description - also dependent on info entered into table RawMaterialList 3) Quantity
My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.
Is there a function or query that I can split a field into different columns?
For example, I have First Name, Last Name, Address.
But I want to split the address field into Address1, Address2, Address3, Address4 as the initial Address field has a lot of characters with commas e.g. 11 London Road, Liketown, Likeshire, London.
So, I want to be Address1: 11 London Road, Address2: Liketown, Address3: Likeshire, Address4: London.
I thought that I can export the field using a simple query and then re-import it with using the text field into columns option, but it is time consuming.
I can't figure out how to skip the first space from the right, and reference to the second occurring space, so that I get "T4T 1L7" to populate the ADD_2 field, and everything to the left of "T4T 1L7" placed in the ADD_1 field.
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
i want to search a phone number in contact information table with column names - number, name, address, dob, gender, f-name, m-name etc and show them in a form with each field in separate text boxes in access 2010.
I need to split a field in my query into two separate fields, e.g fullname into a first and last name, so "John Smith" will appear in two columns, "John" and "Smith".
I have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.
This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.
The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.
I have a txt file that I'd like to import, which contains answers to a test. The number of questions on the test varies year to year, but this year there are 110 (split in to two sections).
I'd like to import the file, so each answer is a field, and then score the answers against a 'correct' answer lookup table.
What's the best way of approaching this? I first thought of using vba to read the text file, split the text, and then use an SQL command to insert to to my table. This seems tedious (there are around 6500 respondents).
I get an error for this code: this is the error, i can firgure out how to have the string not be the textbox, but sub in the texbox name.
Code: myFormstr = Array("F_Value1", "F_Value2", "F_Value3", "F_Value4", "F_Value", "F_Value6", "F_Value7") For Each mystr In myFormstr me.cboProduct = Me.mystr Next mystr
I have a table where I want to break longer strings into three-character strings.
So... for example the data item "Forest," I'd like to generate "FOR" "ORE" "RES" "EST" and write all four to another table, allowing for duplication. Alternately, we could make a list and just count the unique 3-character strings. The end goal here is to measure the frequency of 3-character strings across a set of data.
I have a Microsoft Access table with the following columns: A,B,C,D,E,F. In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck" What I would like to do is parse this string as such:
Whenever I create a combobox, Access asks me if I want to grab the values from another table, or if I just want to manually input the possible values.
My question is: is one of the two solutions "better" than the other?
Let's imagine I am creating a Form where I can create a new Contact, and in a listbox (with multiple selections allowed) I want to list the possible character traits, so I have
I think that if I need to store in the Contact records these possible traits, I will have to create a Many-to-Many relationship (therefor creating a junction table) between Contact and Traits.
On the other hand, could I do everything without grabbing values from an extra table and directly inputting the values in the listbox? How would these elements be stored in the Contact record if I allow multiple values in one single field?
I have imported an Excel table with a column which has several values in one cell separated by "". In my case names of institutions afiliated with a patent. Example of the format of a single cell: MASSACHUSETTS INSTITUTE OF TECHNOLOGY (US) RIVE TECHNOLOGY INC (US)
In order to normalize the Access database I would like to extract the institutions to a separate table (institutions) and that the patent is related to both institutions.
This is my first post of hopefully many, and I hope to be able to keep visiting and helping others in the future.
I'm building a database at the moment which I've done basing forms on Queries as I'm very familiar with doing so. However, one form in the database if a bit different.
The form in question is for shipping off refurbished units at the warehouse, by adding a SHIP ID to the record for each unit scanned in. The form before creates a recird ubdexed by a SHIP ID in the SHIPPING table, along with some other info such as delivery address and ship date. Each unit will be assigned this SHIP ID by the child form I wish to create, by scanning the Serial Number against it. Two actions are done on the child form: Read in Serial Number of unit, find record in MAIN table and display key fields such as the units status (i.e. "WIP" or "REPAIRED" into text boxes on the form Allow unit to be shipped (by assigning the SHIP ID to the unit's record in the main table, only if the STATUS field shows as "REPAIRED" Now, because I want to scan units into the form's Serial Number textbox and do the STATUS field check, then assign the SHIP ID; I need a recordset, right? I've inherited development of datdbases which used recordsets in their forms before, but have never had to create one from scratch. Also I wasn't able to take a copy of the source code of those databases before I left my last company.
Can anyone give me some info on what the bare minimum is to create a recordset, and to use it to save the record once I've made changes.
For your info I would consider myself an Access intermediate, and I've worked with it in various roles and versions for around 5 years.
What is the best solution to this problem? How can I automatically assign a numerical value to a field based on the information currently contained as test in another field? For example, if I have a field that ranks a film as “Poor, Fair, Good, Great, Superior”, how can I have a set value appear for statistical purposes that assigns a number to each of these values (Something like: If rating of current record = “Poor”, then ranking = “1”; If rating of current record = “Fair”, then ranking = “2”; If rating of current record = “good”, then ranking = “3”, etc. I also want to be able to have the values recalculate if someone changes their original opinion.
In a form is there a way to change sort order based on which field is active?
I'm using a Split Form which gives me the ability to sort by clicking on the header name ans selecting sort. This slowing things done since my workload is heavy.
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then Me.txtMaxOrdLimit.Visible = True Else Me.txtMaxOrdLimit.Visible = False End If
I would like to export each record of an access table to a separate text file and use one field of each record as the source for the file name of each exported text file. Does anyone know a way of dong this?
