Convert detail record values to text 1

[migv1]

I need to extract multiple detail record values in a subform and concatenate them into a single text value for transfer to another field. This is intended to make it easier for users to enter prospective payment schedules in a contract creation and tracking database.

Currently, users are expected to enter a narrative version of prospective payment terms for inclusion in legal contracts in addition to entering the same information in a table for tracking in our database. In a simplified system, users would only have to enter the prospective payment terms in the table, and this information would be extracted and converted to the required narrative for the contract.

For example, the subform containing payment info for a contract worth $100,000 would have the following information in the detail section:

Milestone % of Total Projected Payment
Start of Agreement 50 $50,000
45 Days 25 $25,000
Completion of Project 25 $25,000

The narrative created from the three detail records above would look like this:

50% at Start of Agreement ($50,000)
25% at 45 Days ($25,000)
25% at Completion of Project ($25,000)

The above text would then be inserted into the appropriate field of a contract request form.

What would be the best way to accomplish this?

Thanks in advance for any help!

 

[Pampers]

Maybe something like this:

=[% of Total] & " at " & [Milestone] & " (" & [Projected Payment] & ")"

Pampers
Keeping it simple can be complicated

 

[migv1]

Perhaps my question was unclear. In the example above, I need to extract data from all three records (or however many there may be for a particular contract) and build a compound text string with multiple lines separated by line feeds. I probably need to loop through the collection of records, but I'm not sure how to do this.

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[migv1]

Thanks, PH! That's exactly what I needed.

Quite a bit of customization went into the final solution, but the FAQ you provided definitely pointed me in the right direction. I also learned that it's not good to use the "%" character in a field name (e.g., "%OfTotal") - it's OK in Access tables and queries, but unacceptable in SQL expressions.