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
|
|||
|
|||
Inputting data from one source to many fields
Hi, this will probably turn out to be a newbie kinda question, but I need to
be able to cut data from one application and then paste this into several fields on a table.. To show you what I mean, I have an example of the format of the data...... 1023134; 18/06; 07:00 - 12:00; MRS SMITH; 9876543; ; 19 STREET RD; SUBURBSVILLE; ACT; 9999; 32 9999999; ; 9382 2222; AS; 11; ABC DEF EDE SSS TGT 2WS ; YADA YADA YADA; ; WOW; Z To go into fields like..... Field 1 1023134 Field 2 18/06 Field 3 07:00 - 12:00 ^v^v^v^v^v^v^v^v^ Field 11 ABC Field 12 DEF Field 13 EDE Field 14 SSS Field 15 TGT Field 16 2WS Field 17 YADA YADA YADA Field 18 Field 19 WOW The data is separated by semi colons, which I figure will be the key to splitting it up. There are parts of it that are separated by spaces that I would like to split up too, a la the ABC DEF EDE part. The problem that I am having, is that no matter what I seem to try, the data still just fills up in the first field. Damn it, this is doing my head in....I need a bourbon.... Now that am aware I know a hell of a lot less about Access that what I thought I did, I would surely appreciate it if anybody can help me out. Maskedman. |
#2
|
|||
|
|||
Inputting data from one source to many fields
Hi Maskedman,
You need to parse the input string into its component values. Each value is delimited with a semicolon and I'll assume that each input string has the same number of elements. In your code, you dim 19 variables to store the elements as you parse them. You can use 19 individual variables, an array, a class, a collection, etc. as you prefer. Then build 2 nested loops (the outer one for each input string and the inner one for each element in the string) In the inner loop, read up to, but not including, the first semicolon. Store the value in the first variable, remove the text and the semicolon from the input string, and repeat until you reach the end. Now you have all 19 values. Watch out for nulls and make sure the datatypes for the columns in the table match your data. Then insert into the table. Insert Into tablename (Field1, Field2 ...) Values (var1, var2 ...) Then clear all the variables. The outer loop now gets the next input string and the inner loop starts again. If you have a lot of records to parse, you should consider writing this in C for better performance, but VBA will get it done. Jay |
Thread Tools | |
Display Modes | |
|
|