Query format field

[strive4peace]

The ID?

no, it needs to be a field where you will only get 1 record. I am assuming in your real data, you want more than one record. but if you only want 1 all the time, you can set the Top Values property of the query to be 1, or Add Top 1 to the SQL after SELECT

 

[strive4peace]

hi Nita

this seems to work:

SELECT JuntaMesAnoValor.IdAdv_DataSusp
, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'"
,"QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor
GROUP BY JuntaMesAnoValor.IdAdv_DataSusp;

don't know why it has extra lines above SELECT when you copy to Clipboard ... delete them after you paste it

 

[Gasman]

I think the clue is in the name of the function.?
It is meant to concatenate related records.?

Normally you would have your 100,200 & 300 in another table and only one of each in that table.?
Then you would use the query on that table with reference to ConcatRelated() to the table you are using now.?

 

[jocph]

I tried the samples and the function works correctly in the Select query.

SELECT JuntaMesAnoValor.IdAdv_DataSusp, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'","QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor;

But then when the Distinct clause is used, the long text value gets cut.

SELECT DISTINCT JuntaMesAnoValor.IdAdv_DataSusp, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'","QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor;

That's what he is asking, I think.

 

[Nita]

hi Nita

this seems to work:

SELECT JuntaMesAnoValor.IdAdv_DataSusp
, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'"
,"QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor
GROUP BY JuntaMesAnoValor.IdAdv_DataSusp;

don't know why it has extra lines above SELECT when you copy to Clipboard ... delete them after you paste it

It worked.
Thank you very much

 

[Nita]

I tried the samples and the function works correctly in the Select query.

SELECT JuntaMesAnoValor.IdAdv_DataSusp, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'","QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor;

But then when the Distinct clause is used, the long text value gets cut.

SELECT DISTINCT JuntaMesAnoValor.IdAdv_DataSusp, ConcatRelated("MesAnoValor","JuntaMesAnoValor","IdAdv_DataSusp = '" & [IdAdv_DataSusp] & "'","QuotasSolicitadas") AS Mensal, Len([Mensal]) AS Tamanho
FROM JuntaMesAnoValor;

That's what he is asking, I think.

Thank you, I didn't knew why it didn't work.

 

[jocph]

Thank you, I didn't knew why it didn't work.

I am not an expert so maybe others could tell why when DISTINCT clause is applied to the SELECT query, the long text gets cut. And how to fix it.

 

[strive4peace]

Here is a page from Allen Browne's site that discussion truncation of Memo fields. Long Text used to be called memo.

http://allenbrowne.com/ser-63.html