Results 1 to 10 of 10
  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51

    Conactenate two fields

    Hello

    I know this has been asked before, but I just can't get it too work for me.
    I'm trying to use the Allen Brownes ConcatRelated, but not sure if that is needed for this or not.

    I have a database with pipes, and their ids, in this I also have a column with the amount of observations on a giving pipe. These observations are on the same pipe but in different rows. I would like to concatenate this into one cell.

    To start of with I have two identical set up queries, with the same columns and types in them, these I join in a union query.



    Then I want to create a query based on that Union where i concatenate the aforementioned rows.

    Probably easier to show in a picture:
    Click image for larger version. 

Name:	Concatenate.png 
Views:	19 
Size:	41.4 KB 
ID:	29321

    Union query is EksportOverblik, where I would like to concatenate the cells Antal observationer if they have the same delledning.

    This is what I tried so far.
    Concatrelated("Antal observationer fordelt på kriterie/type";"[EksportOverblik]";"DelledningsID = " & [DelledningsID])

    The picture would then read.
    Diameter Ledningsmateriale FysiskIndeks Antal observationer fordelt på kriterie/type Saneringsmetode Saneres/Vurderes Bemærkning DelledningsID LedningsID
    250 Beton 8,8 VA: 2, RB: 3 Column is irrelevant so will be deleteddelete 383 383
    550 Beton 8,8 VA: 2, PH: 3 Column is irrelevant so will be deleteddelete 399 399

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In Allen Browne's explanation of the Arguments, he says to add square brackets ([]) around field names where there are special characters. This could be the problem.

  3. #3
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Quote Originally Posted by aytee111 View Post
    In Allen Browne's explanation of the Arguments, he says to add square brackets ([]) around field names where there are special characters. This could be the problem.
    Tried your suggestion and changed my query to this:
    ConcatRelated("[Antal observationer fordelt på kriterie/type]";"[EksportOverblik]";"DelledningsID = " & [DelledningsID])

    Also tried
    ConcatRelated("[Antal observationer fordelt på kriterie/type]";"[EksportOverblik]";"DelledningsID = " & [EksportOverblik].[DelledningsID])

    and

    ConcatRelated("[Antal observationer fordelt på kriterie/type]";"[EksportOverblik]";"DelledningsID";[EksportOverblik].[DelledningsID])

    Neither works unfortunately. I'm sure it's just a tiny thing I'm doing wrong.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I know that Access does not like special characters, remove the "/" in the name.

    Debug Allen's code: at the beginning add Debug.Print strField & " " & strTable & " " & strWhere. See what those fields contain, whether they contain the correct values.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    For testing purposes, add a criteria to your query of DelledningsID=383, then you won't have to go thru all the records while troubleshooting.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What does 'not working' mean - error message, wrong results, nothing happens?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Advise no spaces or punctuation/special characters (underscore only exception) in names. That Antal... field name is probably longest I've ever seen.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Quote Originally Posted by June7 View Post
    What does 'not working' mean - error message, wrong results, nothing happens?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Advise no spaces or punctuation/special characters (underscore only exception) in names. That Antal... field name is probably longest I've ever seen.
    Pardon me, the first rules of bughunting is to supply the error messages, my bad.

    Currently I'm getting Error 3070: The Microsoft Access database engine does not recognize '383' as a valid field name or expression.
    Previous to that, I got Error 3061: Too few parameters. Expected 1.

    I will see if I can't upload a sample database for testing, but there is a lot of thing I would need to remove.
    Before that I have already reduced the name to "Antal Observationer af typen"

  8. #8
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Added my database.

    The arrows show which modules and queries I'm using.

    Click image for larger version. 

Name:	Explanation.jpg 
Views:	13 
Size:	79.0 KB 
ID:	29330


    Testddb.zip

    Again, thank you very much for the help.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I never use dynamic parameterized queries. That is source of the problem here. The function is trying to open a recordset but cannot provide a parameter for [Indtast Projektnavn] filter criteria. That is the cause of the 'Too few parameters. Expected 1' error message. Remove the dynamic parameters and Tester runs.

    With the 383 filter parameter, the output is 2 identical records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    51
    Quote Originally Posted by June7 View Post
    I never use dynamic parameterized queries. That is source of the problem here. The function is trying to open a recordset but cannot provide a parameter for [Indtast Projektnavn] filter criteria. That is the cause of the 'Too few parameters. Expected 1' error message. Remove the dynamic parameters and Tester runs.

    With the 383 filter criteria, the output is 2 identical records.
    Wow, such a simple thing. You are absolutely correct. I do need that unfortunatly, but moving it to this query instead, solves that issue.

    Thank you very much for the help, much appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-24-2016, 07:24 AM
  2. Replies: 3
    Last Post: 05-03-2014, 11:00 AM
  3. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  4. Replies: 4
    Last Post: 05-14-2012, 06:10 PM
  5. Replies: 12
    Last Post: 05-07-2012, 12:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums