Undefined function in 'Conc' expression
- Thread starter melanie
- Start date
- Local time
- Today, 08:26
- Joined
- Feb 28, 2001
- Messages
- 28,278
After reading this thread, I believe you are approaching this all wrong. You don't want to concatenate ANYTHING. You want a record involving grouping with the individual descriptive sections as the detail records in a report with group-header to bring together like items.
I just read this thread too, and the Conc function, creates a
comma-seperated string of all child record values.
It rounds them up through the parameters:
FieldName, PrimaryKeyName, PrimaryKeyValue and TableName.
If you change the functions SQL to "Select Distinct FieldName ..."
it will remove duplicates in the resultant string.
Wayne
comma-seperated string of all child record values.
It rounds them up through the parameters:
FieldName, PrimaryKeyName, PrimaryKeyValue and TableName.
If you change the functions SQL to "Select Distinct FieldName ..."
it will remove duplicates in the resultant string.
Wayne
melanie
A Walking Contradiction
- Local time
- Today, 16:26
- Joined
- Jun 6, 2003
- Messages
- 101
DocMan: Thanks for the interest in this thread.
I was running a totals query where I totalled the value of one invoice. The invoice consists of many different rows with different fees per cost centre. (so one bank invoice - many services rendedered to different bank branches making up the invoice entry). In order to still retain the cost centre field and have only one summed entry per invoice, I needed to concatenate the rows into one field.
The Conc() function worked perfectly to achieve the result I was after. I have probably not explained very well, but adding the "distinct" to the function gave me the exact result. It removed the duplicates as WayneRyan described in his thread, so I only had one instance of each item instead of the item being repeated for as many records it matched in the tables:
So instead of cpt, cpt, cpt, jhb, pretoria (5 records) I got the following result:
cpt, jhb, pretoria. Perfect! Thanks again to RuralGuy for the help.
WayneRyan: Thanks too for your post, that was the right solution!!
Kind regards
Melanie
I was running a totals query where I totalled the value of one invoice. The invoice consists of many different rows with different fees per cost centre. (so one bank invoice - many services rendedered to different bank branches making up the invoice entry). In order to still retain the cost centre field and have only one summed entry per invoice, I needed to concatenate the rows into one field.
The Conc() function worked perfectly to achieve the result I was after. I have probably not explained very well, but adding the "distinct" to the function gave me the exact result. It removed the duplicates as WayneRyan described in his thread, so I only had one instance of each item instead of the item being repeated for as many records it matched in the tables:
So instead of cpt, cpt, cpt, jhb, pretoria (5 records) I got the following result:
cpt, jhb, pretoria. Perfect! Thanks again to RuralGuy for the help.
WayneRyan: Thanks too for your post, that was the right solution!!
Kind regards
Melanie
Similar threads
