Undefined function in 'Conc' expression

Please, Please tell me where I'm going wrong. I have read up and looked at examples of concatenating multiple rows into one, but I receive the error message " Undefined function in 'Conc' expression when I try to run the query.

Below is the sql:

SELECT testconc.InvoiceNo, Conc([InvoiceNo],"CostCentre") AS CostCentre
FROM testconc
GROUP BY testconc.InvoiceNo;

Please can someone put me out of my misery?

Many thanks
Melanie

Hi Melanie,
Can you post the code for the Conc() function?

Hello my dear friend, RG

Its the Conc() function on the query, sql as above.

Regards
Melanie

AFAIK there is *no* Conc() function in Access. What is it supposed to do?

Its for concatenation

I found an example which I was working from...

SELECT [tblData].[ID], Conc("Field1","ID",[ID],"tblData") AS Field1, Conc("Field2","ID",[ID],"tblData") AS Field2
FROM tblData
GROUP BY [tblData].[ID];
The above is taken from a sample db which I'll post

melanie said:

Its for concatenation

I found an example which I was working from...

Click to expand...

That would be a custom function that someone wrote and put in a module. If you don't have that function in a module, you can't use it.

So, you have to go the old-fashioned route.

I just checked SQL BooksOnLine and there is no Conc() function there either. What is the example you found?

From this db, I am trying to achieve what they are doing here

RG I have to go out for a bit, will be back later. What I am trying to do is to concatenate all the costcentres for an invoice into one text field. Each row in the invoice is for a different valuation and sometimes different cost centres, so I want one invoice to list all the cost centres and a total invoice amount.

Thanks RG

Do you have the Module1 code in a standard module in your db? That is where the Conc() function is located.

Rg I will check when I get back, is that where you think the problem lies?

Probably, but it does compile so we'll see you when we get back.

I suspect the problem is that the Conc() function *requires* 4 parameters. You are only supplying 2 in your SQL statement.

Thanks for the responses RG and Bob.

What would the "old fashioned" route be? If I can't use the conc() method on my query to get a list of the rows how can I go about it?

Regards
Melanie

I found this on a link on one of the threads, but this pertains to a report and not to the query:

Add the following code to the detail section's OnFormat property [Event procedure]: Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail1_Format_Err
If Not FirstPass Then
Me!AllProducts = Me![ProductName]
FirstPass = True
Else
Me!AllProducts = Me!AllProducts & ", " & Me![ProductName]
End If
Detail1_Format_End:
Exit Sub
Detail1_Format_Err:
MsgBox Error$
Resume Detail1_Format_End
End Sub


11. Close and save the report.
12. Open the report in Print Preview. Note that the following data is displayed in vertical format.Category: Beverages Products: Chai, Chang, Chartreuse
verte, Cote de Blaye,
Guarana Fantastica, Ipoh
Coffee, Lakkalikoori,
Laughing Lumberjack
Lager, Outback Lager,
Rhonbrau Klosterbier,
Sasquatch Ale, Steeleye
Stout

Category: Condiments Products: Aniseed Syrup, Chef
Anton's Cajun Seasoning,
Chef Anton's Gumbo Mix,
Genen Shouyu, Grandma's
Boysenberry Spread,
Gula Malacca, Louisiana
Fiery Hot Pepper Sauce,
Louisiana Hot Spiced
Okra, Northwoods
Cranberry Sauce,
Original Frankfurter
grune Sobe, Sirop
d'erable, Vegie-spread


I can't see the wood for the trees.

Ok, I have managed, partly to achieve what I want to do. I added the function to module 1 and got this result with the following sql:

SELECT DISTINCT qryBankInvTotal.InvoiceNo, qryBankInvTotal.BankID, Sum(qryBankInvTotal.ValuationFee) AS SumOfValuationFee, Sum(qryBankInvTotal.TravelFee) AS SumOfTravelFee, Sum(qryBankInvTotal.TotValuationFee) AS SumOfTotValuationFee, Conc("CostCentre","InvoiceNo",[InvoiceNo],"qryBankInvTotal") AS CostCentre
FROM qryBankInvTotal
GROUP BY qryBankInvTotal.InvoiceNo, qryBankInvTotal.BankID;

Listed now are repeated items of the costs centres. Is there a way to only get the unique rows on the list?

Thanks for pointing me in the right direction.

Melanie

So is every repeated row EXACTLY the same? If so, I suspect that you haven't joined all of the applicable fields and are getting a cartesian result.

melanie said:

What would the "old fashioned" route be? If I can't use the conc() method on my query to get a list of the rows how can I go about it?

Click to expand...

FYI:

Bob, the fields are correct, there isn't a cartesian result.

I'll explain what is happening:

I have an invoice, this invoice contains a list of different jobs by idno. The invoices sometimes are sent to a bank (all bank branches included) and sometimes are only sent to the particular branch which requested the service.

So we can have one invoice that has multiple listings for a certain branch (Cost Centre)

Inv001 = Id0102001 - Bank = xxx Cost Centre = N\A
Inv001 = Id0102002 - Bank = xxx Cost Centre = N\A

Inv002 = id0102003 - Bank = yyy Cost Centre = A
Inv002 = id0102004 - Bank = yyy Cost Centre = A
Inv002 = id0102005 - Bank = yyy Cost Centre = B
Inv002 = id0102006 - Bank = yyy Cost Centre = C


The Result I am trying to achieve is as follows:

Invoice No Bank Branch Fee

001 XXX N/A R-,--
002 YYY A; B;C R-,--

At the moment I am getting the following result from the query:

001 XXX N/A; N/A R-,--
002 YYY A; A; B; C R-,--

I don't want the duplicate "A" on invoice 002 to show or the duplicate "n/a" on invoice 001 to show.

Many thanks for coming back to me. It is much appreciated.