Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating (Access 2003)

    Hello

    I have a query where for some records all fields are identical apart from the 'passenger name' field which can be different each time. I am trying to concanenate these names in to a new field so that each record just appears once with the passenger names in one field, perhaps seperated by commas.

    You previously referred me back to another post for the VBA code for the function 'Concat'. I have copied this in to a standard module as suggested. How do i now use this in the query to create the new concanenated field? I couldn't quite work it out from the example given in the previous post.

    Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    You probably need to create a Totals query that groups on some fields, and uses the Concat function to concatenate the passenger names. Could you provide some details?
    - What is the name of the table or query?
    - What are the names of the fields to group on (i.e. the fields whose values stay the same while the passenger name varies)

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Thanks.

    The query is called 'All Vehicles QRY'.

    The fields are: Registration Number, Expr1(x current vehicles QRY), Expr1(x current drivers), Accquired from, Date accquired, Expr1 (x final fuel cards), Office / site name, Weekly hire rate / equivalent, Exp1 (x current passengers), Status, Location Category, Employee Category.

    The passenger name field which i would like to concatenate is 'Expr1 (x current passengers).

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    I'm sorry, how can one query have multiple fields named Expr1?

  5. #5
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Because they all originate from other queries. Each one has a different query name but is called Expr 1 from when it was created in the individual query.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    It's always better to give fields meaningful names, but OK.
    Do you want to group on all other fields? If so, try the following:

    Create a new query based on 'All Vehicles QRY'.
    Select View | Totals to change the query into a totals query.
    Add the fields you need, except for
    Add a calculated column:

    Passengers: Concat("All Vehicles QRY", "Expr1 (x current passengers)", "[Registration Number]=" & Chr(34) & [Registration Number] & Chr(34))

    and set the Total option for this column to Expression (the others are all Group By).

  7. #7
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Thanks.

    Is the expression 'Concat( etc etc.' to be typed into the field row of the query? That's where i put it and it cam back with an error 'undefined function Concat'. Am i typing the expression into the wrong place or have i incorrectly saved the Concat VBA? Thought i'd saved it as a standard module.

    Thanks again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    If you saved the Concat function in a standard module, you should be able to use it in a query (in the Field: row). Make sure that the function is defined as Public, not as Private:

    Public Function Concat(...)
    ...
    End Function

  9. #9
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Hello.

    Have now taken the following steps:

    1. Saved the function Concat code that you sent in to a module. This starts "Public Function Concat _" and ends "End Function".

    2. Inserted the following code in to the 'field' row of a query:

    Expr1: Concat("all vehicles QRY","expr1 (x current passengers)","[registration number]=" & Chr(34) & [registration number] & Chr(34)).

    When i try and run the query it still comes up with an error saying 'undefined function Concat'.

    Does this mean it is not recognising the Concat function? What have i done wrong?

    Is there some way i can test if Concat is correctly defined as a standard module? If there is some way of resolving this without posting a copy of the database i would be grateful, it took me ages to make the file small enough last time!!!

    Thanks.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    Are you sure that the Concat function is in a standard module, i.e. the type you create by clicking New in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor? It should NOT be in a class module, or in the module belonging to a form or report.

  11. #11
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Yes. Quite sure. Have just copied it in to another module by doing 'new' in the modules window just to be sure!!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    I'm sorry, but I can't offer more help without seeing the database. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  13. #13
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    OK then!!

    Here is the file (hopefully now small enough).

    The module is called Concat and the query is called Concat QRY. The field i am trying to concatenate is Passenger Name.

    Thank you.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenating (Access 2003)

    One problem is that you have named one of your modules Concat. This confuses Access - the error message is admittedly very non-informative. Rename the module.

    Also, you have 4 copies of the Concat function in different modules now. Remove all except one, so that Concat is unique.

    You used an invalid field name in the expression; I had to change the field name Expr1 in X Current Passengers QRY to be able to handle it.

    I modified the Concat function slightly, because blanks in the passenger name field weren't really blanks, but spaces.

    See attached version.

    If you ever have some spare time on a rainy afternoon, consider giving your database objects and fields more practical names; all those spaces make things very difficult, as does having Expr1 fields in multiple quieries.

  15. #15
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating (Access 2003)

    Thanks very much, that worked perfectly - you're a star as usual.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •