If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Strings: Parsing data into other fields in the same table
I am making a simple database and I was wondering how can
the information from a field containing strings of both numbers and characters be broken and entered other fields. I am dealing with a code that looks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics). I have a field for the code and fields for each of the four parts but I was wondering how I could automate the entry of the four extra fields as the data is redundant. |
#2
|
|||
|
|||
Strings: Parsing data into other fields in the same table
I hope I am understanding your question correctly. Anyway, the following
will work if your field is consistently four characters. On the After Update event of your code field put the following: Me.CodeYear = Left([code],1) Me.CodeSeason = Mid([code],2,1) Me.CodeMedia = Mid([code],3,1) Me.CodeGenre = Right([code],1) This will separate that four digit field into four different fields (you will have to use your own field names). Hope this helps. " wrote: I am making a simple database and I was wondering how can the information from a field containing strings of both numbers and characters be broken and entered other fields. I am dealing with a code that looks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics). I have a field for the code and fields for each of the four parts but I was wondering how I could automate the entry of the four extra fields as the data is redundant. |
#3
|
|||
|
|||
Strings: Parsing data into other fields in the same table
Thanks for your help! I have one more question though
which you seemed to note. What do I do if for the code isn't consistently four characters. What if two fields fluctuate between having 1-2 and 3-4 characters respectively? -----Original Message----- I hope I am understanding your question correctly. Anyway, the following will work if your field is consistently four characters. On the After Update event of your code field put the following: Me.CodeYear = Left([code],1) Me.CodeSeason = Mid([code],2,1) Me.CodeMedia = Mid([code],3,1) Me.CodeGenre = Right([code],1) This will separate that four digit field into four different fields (you will have to use your own field names). Hope this helps. " wrote: I am making a simple database and I was wondering how can the information from a field containing strings of both numbers and characters be broken and entered other fields. I am dealing with a code that looks like this: 01MP, and has four parts. The first number refers to the year 2000, the second number refers the season of the year (spring), the third to type of media (a magazine), and the fourth the genre (politics). I have a field for the code and fields for each of the four parts but I was wondering how I could automate the entry of the four extra fields as the data is redundant. . |
#4
|
|||
|
|||
Strings: Parsing data into other fields in the same table
wrote in
: I have a field for the code and fields for each of the four parts but I was wondering how I could automate the entry of the four extra fields as the data is redundant. You would be better off having four fields for the parts and getting rid of the four-char field altogether. It is easy enough to populate the four short ones from existing data -- this is a single update query (see help for details) using a Mid() function (ditto help). It is easy enough to create a text box to display the code as a single string (use the ControlSource property) on a form or report. The trick is to develop a smooth and idiot-proof method of allowing your users to enter it. If they will go for four controls (eg textboxes, dropdowns, or whatever) then your problems are over. If they insist on entering four bits of information into one box then you'll have to do a little bit of VBA trickery to validate it and then chop it up. This kind of might work ' the txtCode control is an Unbound Text Box. You can fill ' it in for existing records using the Current event ' private sub txtCode_BeforeUpdate(Cancel as Integer) if mid(txtCode,1,1) in ("0", "1", "2", "3", "4", "5") Then me!yeardigit = CInt(mid(txtCode(1,1))) else ' no good value Cancel = True end if if mid(txtCode,2,1) in ("1", "2", "3", "4") Then me!seasondigit = CInt(mid(txtCode(1,1))) else ' no good value Cancel = True end if if ' etc, checking the other two digits end if if Cancel = True then ' warn the user msgbox "That code is no good!" end if ' setting the Cancel flag will prevent user from moving off, so ' you don't have to do anything else End Sub Hope that helps Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to extract original data from a pivot table? | cmagnus98 | General Discussion | 1 | July 9th, 2004 03:55 PM |
Trying to break up data in the label format to fields, for easy entry into table | Visali | Mailmerge | 1 | June 7th, 2004 10:35 PM |
Combining data from multiple fields into Pivot table | Os | Worksheet Functions | 5 | June 1st, 2004 05:04 PM |
HELP: Update 1 table with calculated fields from another table | Samora | Running & Setting Up Queries | 0 | May 23rd, 2004 05:16 PM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |