• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Query format field (1 Viewer)

strive4peace

AWF VIP
Local time
Today, 10:11
Joined
Apr 3, 2020
Messages
571

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
PropertySheet_Query_TopValues_1_SQL_Select top 1.png
 

strive4peace

AWF VIP
Local time
Today, 10:11
Joined
Apr 3, 2020
Messages
571
hi Nita

this seems to work:
SQL:
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

Enthusiastic Amateur
Local time
Today, 16:11
Joined
Sep 21, 2011
Messages
6,759
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

Member
Local time
Today, 23:11
Joined
Sep 12, 2014
Messages
52
I tried the samples and the function works correctly in the Select query.

Code:
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.

Code:
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

New member
Local time
Today, 16:11
Joined
Feb 14, 2020
Messages
19
hi Nita

this seems to work:
SQL:
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

New member
Local time
Today, 16:11
Joined
Feb 14, 2020
Messages
19
I tried the samples and the function works correctly in the Select query.

Code:
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.

Code:
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

Member
Local time
Today, 23:11
Joined
Sep 12, 2014
Messages
52
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.
 

Users who are viewing this thread

Top Bottom