[FONT="Courier New"]My example is not really a correct example.
The problem is that the some portion of the Credit column, overlaps the debit column. It looks something like this (where _ is actually a space)
I have this Accounting Report wherein the Debit and Credit is overlapping.
GL CODE GL ACCT NAME DEBIT CREDIT
999-000001-01 GL Account 1 1,000.00
999-000002-02 GL Account 2 1,000,000.00
999-000003-03 GL Account 3 100,000.00
999-000004-04 GL Account 4 10,000,000.00
How can i trap these kind of report. Please HELP!:confused:[/FONT][/QUOTE]
Hi Krokie and welcome to the forum.
Hmm, that's and interesting design of report. Presumably the designer had a reason ... but then again maybe not. I assume this is an ASCii report not a conversion form a pdf?
There could be several approaches to this all of them very dependent on the specific physical layout as to what would work best.
Assuming that the decimal points are always in the same place for each column (and not in the same place for both columns)AND that you never have both a debit and a credit on the same line AND that there is no other identifier for the line type (DR/CR) then I would be tempted to extract BOTH columns as a single CHARACTER field to start with.
Then a calculated field assessing the position of th decimal point using the INSTR() function will, hopefully, tell you whether the value is a credit or debit and allow you to convert it to an appropriate numeric field.
The VAL() and IF() functions will be in play for that.
If you have not used the function previously check out the HELP entries as a first step. If that still leaves you unsure of how to progress - and it might since this isort of slice and dice is not everyday stuff - let us know.
I've got another problem here.. this is my report:
1.0 GROUP HEADER
1.1 Sub Group 1
1.2 Sub Group 2
2.0 GROUP HEADER
2.1 Sub Group 1
3.0 GROUP HEADER
3.1 Sub Group 1
3.2 Sub Group 2
How can i capture the GroupCode/Desc and SubGroupCode/Desc if they fall on the same column? Thanks..
If you do need to capture both lines as appends to the detail you will need to find something on the lines that differentiates them and allows separate capture.
Alternatively - do you really need both?
Can a 1.0 line's information be interpreted from information on a 1.1 line for example? If so can you trap just the 1.1 type lines and create a calculated field?
Alternatively could you use a lookup from a 1.1 type line to provide the relevant data from what I am guessing is a fairly static entry for 1.0 lines?
Unless you have some consistent formatting to work with these may become rather data dependent traps.
I started using Monarch back in the DOS days and confess to doing things the hard way sometimes. I have not used the "not" trap before. I put your trap together in a model using a floating trap - for when the numbers get to nn.0 and nnn.0, etc. and it works great. Much better than a two step approach. Thanks for the tip.