Puzzling over Text Format (1 Viewer)

[dynamictiger]

Hi All

I have a table which contains a scope of works for some job we are doing. The data is entered in the format similar to:

1: Open the door
2: Walk through the door
3: Close the door

I am processing this through VBA and adding other variables in such as quoted amount, and variations etc. Dressing it in a msg to something like:

Thank you for the opportunity to provide the following:
1: Open the door
2: Walk through the door
3: Close the door
As quoted for $100.00 + G.S.T.

variations on this job were:
Fitted a new door $600.00 + G.S.T.

Total for this invoice $700.00 + G.S.T

Then I need to output it to a text file as our accounting system only supports text files in the invoice wording section.

Using the following code:

 CopyTextToClipboard strWording
    
    Debug.Print GetTextFromClipboard()

    Open "C:\Users\David\Documents\MYTESTFILE.TXT" For Output As #1
    Print #1, GetTextFromClipboard()
    Close #1

Or as an alternative:

Open "C:\Users\David\Documents\MYTESTFILE.TXT" For Output As #1
            Write #1, strWording
            Close #1

In either version I end up with:

Thank you for the opportunity to provide the following:

1: Open the door2: Walk through the door3: Close the doorAs quoted for $100.00 + G.S.T.

variations on this job were:
Fitted a new door $600.00 + G.S.T.

Total for this invoice $700.00 + G.S.T

As the scope is a constantly variable length and content I am trying to find a simple way to control the formatting so the text file outputs as we want it to be on printing on the clients invoice.

Any suggestions?

 

[TJPoorman]

What is the code that is getting your 1:, 2:, and 3:? Basically, you need to add a carriage return at the end of each item Chr(13)

 

[dynamictiger]

This isn't code generated otherwise it would be easy.

The user generates the task list in word or some other program. Copies and pastes it to a custom web application. The web application outputs the data when required for invoicing in CSV format. I bring into access and process to the accounting software.

So the numbers are added at the beginning of the process and not written by access.

To make life even better the numbers are not constant either. Some lists have them, some don't etc.

Thanks for the thought

 

[scooterg]

I would just make a table using a Value List for the elements you need on the invoice. Add A mutil line field for you text info on the invoice

 

[TJPoorman]

Can you provide a copy of the raw CSV data?