Help!-- Parsing Character Separated String

[ConfusedNAccess]

Bear with me- I'm a novice when it comes to this subject, I've never been "formally educated" in Access or VBA. I'm learning as I go. So my description may be muddy.

I've got access tables linked to an ODBC database -a program that performs analysis- data is entered for multiple variables in an excel spreadsheet inside the program. Values are entered for multiple variables. These variables have underlying formulas and are automatically calculated in the excel spreadsheet.

These calcualted values are separated by months 1-12 on the spreadsheet (columns). This data is present in a a character separated string inside the ODBC Database in a table. There are 2 tables present- 1)Datatable- shows all the data and a corresponding table- which has fields for defining the layout of the text string in the data table.


ex.DefinitionTable
column1-LayoutVersion---10,11,12
column2-LayoutDefinition---
("10") Title|StandardText

("11")Active|InDefault|GracePeriod|DateDue|DateReceived|Received|SignoffNote

("12")Active|InDefault|RequiredValue|ActualValue|SignoffNote
DataTable
column1-LayoutVersion---10,11,12
column2-Data-- ex.(layoutversion12)(N|N|||~Y|Y|4.0000|14.7083|~N|N|||~N|N|||~N|N|||~N|N|||~N|N|||~Y|N|0.0000|0.0000|~N|N|||~N|N|||~N|N|||~N|N|||~N|N|||
My question--

How do I parse (I guess this is the correct term)this data into fields for reporting? I am somewhat a novice
From what I can tell, the delimiter "~" separates the month from the next.

I'm really not sure of the best route- To be honest I don't know of any route. Again, I have no education on this matter.

I need to separate the character separated string into multiple columns based on the definiton of the value and then separated by month. In text string above, please note there are 13 months of data. THere are columns present in that table as well noting the number of reporting months as well as the last month to count back from.

This is the only indication that I can tell of how to define which month the data belongs to.

I need someone with knowledge of this, and PATIENCE to walk me through this. Thanks for reading.

 

[SkipVought]

Hi CNA,

If this is ACESS VBA, please poar in Microsoft: Access Modules (VBA Coding) forum705.

Skip,
[sub]
[red][/red]
[/sub]

 

[stevexff]

OK, so you have one table which contains what amount to data dictionary descriptor entries, an another with the actual data. The key that links them together is the LayoutVersion. Urgh.

You can use the Split function to return an array of items split from a string a specific delimiter, in this case a tilde (~).

Then you can iterate over these with a For Each, splitting them again on the '|' to get the individual fields. What you do with them then is up to you. It's easy enough to set the values of cells with this information.

Note that because the dictionary table and the data table have the same format, you could even get clever and use the same subroutine to parse them. This would allow you to populate the column headings first, followed by the data rows. You'd probably need to separate the different layoutVersions onto different sheets for this to make any sense, however.

read the

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[ConfusedNAccess]

This is awesome! How do I got about doing this?