Inputting data from one source to many fields

[Maskedman]

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.

 

[Jay Vinton]

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