Help please? - parsing multiple data items from one field to others

[vbaInet]

I know you love Excel There are benefits though since the Access Import gets rid of all the nasties you might encounter if doing it directly.

I'll let you and Allan carry on

 

[Brianwarnock]

Sorry I changed my reply as you posted, but the feelings remain the same.

You youngsters, ie under 60, are too quick for me.

Brian

 

[vbaInet]

Why not below 80? Or would that be getting to close to your age?

Would you have had a change of mind if it were a text document?

 

[ccotti]

Ok.. here is some data I threw together really quick.. while it is all fake.. it is in the same format as the real data.

I hope it helps.. and, please feel free to let me know if there is anything you may need.

Chris

 

[ccotti]

Without your Db and with no comment on what and where the error the only thing I can point at is this

Dim intaltsub_number As String

should be

Dim lmgaltsub_number As Long

but in principle your approach to adding the extra field is correct.

Brian

Thanks, Brian, that makes me feel better that at least the approach was on track.. I used String over Long because that field will contain alphanumeric data. Anyway, you can see the format in the DB I just uploaded.
Chris

 

[Poppa Smurf]

Brian

You youngsters, ie under 60, are too quick for me.

I am not a youngster.

Re Bloating
Bloating occurs when you run a procedure and it causes the size of the database to increase in size due to space allocated during the running of the procedure. To reduce the bloat you need to do a Compact and Repair. In my case at work we were running the same process several times a day and each day the size increased by 10 Meg. The module was transposing data in a table.

Transposing is a process to show the data in a table or query so that the field names are listed vertically down the left column and the data extends across the page To overcome this we used arrays to do the data manipulation of transposing.

 

[Brianwarnock]

Allan On the age issue I was addressing vbaInet, we have joked about his versus my typing speeds before.

On bloat had assumed correctly what you were talking about but wanted to be sure, I am not convinced that it would be an issue in this case and I would prefer to risk it rather than jave to guess at the array size.

Chris I do not have 2007.

Brian

 

[Poppa Smurf]

Brian

I agree with you speed decreases with age, or in my case I tend to type fast but end up with spelling errors the becomes teh etc.

I have attached a 2003 version of Chris's database. I found a problem when I ran your module there is a field in the output file called Id set to a number and it is the Primary Key. When I ran your module and error message was displayed the Primary key was null or a similar message. Removing the ID field there was no error.

Have fun, let me know if you need help.

 

[Brianwarnock]

Thanks Allan I'll take a look when I can.

Brian

 

[Brianwarnock]

Fortunately that was simple BUC_ASC can be a zero length field, so I have put code in to handle that.
Also I have changed the data definitions in the output table to numbers for those that are and changed intaltsub_number to straltsub_number as it is a string.

I have made the ID an autonumber and therefore not included it in the code, if you wish it to always start at 1 then you can handle this yourself in the output loop after changing it to a number in the table.

Brian

 

[ccotti]

Ok.. I've been playing with the DB and have been able to parse out the additional column (BUC_ASC) into two columns (AltSub_Num & AltSub_Name). I added the code for the column input and column outputs as I did before and changed "recordnumber = 1 to recordnumber = 2". When I ran it, I started seeing data in the two output columns. However, I get an error "Run-time error '5': Invalid procedure call or agrument".

When I took a look at the data and moused over the code (to get the popup), I noticed that it seems to be failing at the point where it runs into a null value. This will be an issue since there will definitly be null values in this column. In other words, we will always have something in the BUC_MC field and (in theory) something in the Plan_Name field. But, the BUC_ASC field may or may not have data in it.

Oh! also, there is data in the column BUC_ASC that has a ":" in place of the "_". It may be "2103:AC2.0:Non-Standard Confirmation Process" and should be "2103_AC2.0:Non-Standard Confirmation Process".

Chris

 

[ccotti]

Fortunately that was simple BUC_ASC can be a zero length field, so I have put code in to handle that.
Also I have changed the data definitions in the output table to numbers for those that are and changed intaltsub_number to straltsub_number as it is a string.

I have made the ID an autonumber and therefore not included it in the code, if you wish it to always start at 1 then you can handle this yourself in the output loop after changing it to a number in the table.

Brian

------------------------
Brian!

You are too good!!

I was just submitting a post and didn't get it out before yours..

Don't let anyone tell you that you are NOT quick enough or have lost a step..

Thanks for the help!

 

[Brianwarnock]

Oh! also, there is data in the column BUC_ASC that has a ":" in place of the "_". It may be "2103:AC2.0:Non-Standard Confirmation Process" and should be "2103_AC2.0:Non-Standard Confirmation Process".

I didn't spot that but it will not cause a problem as we are spliting this field with instr technique and only look for the first :

Brian

 

[Brianwarnock]

Happy to help Chris, it keeps the old grey matter working.

Brian

 

[ccotti]

It's working great!!

And, the data issue was due to a typo.. so, no problem on that..

Thanks everyone!!

I hope you all are paid back for this a 1000 times over!

Chris