ConcatRelated in a query.

[tucker61]

I am using The Allen Browne ConCatRelated VBA code and this has worked well for me for a number of years.

I now want to take the Concat Field and store this data in a new field. - And i am struggling to do so.

Current Code -

tbInvoice = Nz(ConcatRelated("Invoice_Number", "TblQCPreRetailDetail", "Job_ID = " & Nz(Currentjob, 0)))

I managed to get a version working but it is not combining the records.

What do i need to change to make this work in a Query ?

UPDATE tblQCPlannedRTMCharges INNER JOIN Temp_Aged_Claims ON tblQCPlannedRTMCharges.Job_ID = Temp_Aged_Claims.Job_ID SET tblQCPlannedRTMCharges.Invoice_Number = ConcatRelated("Invoice_Number","Temp_Aged_claims","Job_ID = " & "Job_ID" & ", 0");

 

[cheekybuddha]

Assuming the query works otherwise, change this part of your statement:

"Job_ID = " & "Job_ID" & "

to

"Job_ID = " & Job_ID"

 

[CJ_London]

Think that should be

"Job_ID = " & Job_ID & "

 

[tucker61]

Think that should be

"Job_ID = " & Job_ID & "

Sorry did not work.

Also Tried

ConcatRelated("Invoice_Number","Temp_Aged_claims","[Temp_Aged_Claims].[Job_ID] = " & [tblQCPlannedRTMCharges].[Job_ID] & "")

And i get all the invoices combined for all jobs.

 

[CJ_London]

better show the field list for your Temp_aged_claims query

 

[cheekybuddha]

Think that should be

"Job_ID = " & Job_ID & "

Thanks for catching that!

I was on a bus and answering from my phone - I must have fat-fingered the selection when trying to edit

 

[cheekybuddha]

@tucker61 - are you sure you really need to store this calculated value?

You can just calculate it on the fly when needed.

 

[Minty]

I have stored this type of thing in an Access DB for alternative part number listings, where they need processing quickly and wouldn't change very often.
On a large table the concatrelated, or equivalent function, gets very slow.