By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

VBA Concatenation Assistance Request

USTRAGNU1
P: 36
Team Bytes,

I have a query that is outputting a few duplicate records because of two fields, [HR Reporting].[Type of Occupational Cert] and [HR reporting].[Date Occupational Cert Issued]. I would like to output one record with the multiple certifications and dates concatenated in one field. Example of current output:

Name Certs Cert Date
Doe Jane CISSP 1/2/03 (the rest of the fields are the same)
Doe Jane SEC+ 2/3/04
Doe Jarl C++ 3/4/05
Doe John Net+ 4/5/06


Would like to display:

Name Certs And Dates
Doe Jane CISSP 1/2/03; SEC+ 2/3/04; (the rest of the fields)
Doe Jarl C++ 3/4/05
Doe John Net+ 4/5/06

Would anyone be able to give an example chunk of VBA code to make this work?

Please let me know if you have questions or require additional information and as always, thank you for your valuable time/effort.

Mike
2 Weeks Ago #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
Probably the best way to do this is to use a User-Defined Function that concatenate those fields into one. If you do a search on this forum for “MS Access VBA Concatenate” you may be able to find several that are useful. I couldn’t find the exact specific one I was looking for, but there are several that can point you in the right direction.

Hope this hepps!
2 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,302
Combining Rows-Opposite of Union may prove helpful.
2 Weeks Ago #3

ADezii
Expert 5K+
P: 8,615
  1. Placing a Full Name in a Field called [Name] is wrong for so many reasons, but that is probably a Topic for discussion at another time.
  2. Sample Data used in Demo:
    Expand|Select|Wrap|Line Numbers
    1. ID    TName      Certs    Cert Date
    2. 1    Doe Jane    CISSP    1 /2 /2003
    3. 2    Doe Jane    SEC+     2 /3 /2004
    4. 3    Doe Jarl    C++      3 /4 /2005
    5. 4    Doe John    NET+     4 /5 /2006
    6. 5    Doe Jane    MCSE     6 /23/2007
    7. 6    Doe Jarl    PAER     12/12/2008
    8. 7    Flintstone Fred        
    9.  
  3. Create the following Query which will list all 'UNIQUE' Names sorted alphabetically and call a Function within a Calculated Field:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblData.TName AS Employee_Name, fConcat([TName]) AS Certification
    2. FROM tblData
    3. ORDER BY fConcat([TName]);
    4.  
  4. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConcat(strName As String) As String
    2. Dim MyDB As DAO.Database
    3. Dim rstConcat As DAO.Recordset
    4. Dim strBuild As String
    5.  
    6. Set MyDB = CurrentDb
    7.  
    8. Set rstConcat = MyDB.OpenRecordset("SELECT * FROM tblDATA WHERE [TName] = '" & strName & "'", dbOpenForwardOnly)
    9.  
    10. With rstConcat
    11.   Do While Not rstConcat.EOF
    12.     strBuild = strBuild & ![Certs] & " " & ![Cert Date] & ";"
    13.       Debug.Print ![TName]
    14.         .MoveNext
    15.   Loop
    16. End With
    17.  
    18. fConcat = Left$(strBuild, Len(strBuild) - 1)
    19.  
    20. rstConcat.Close
    21. Set rstConcat = Nothing
    22. End Function
    23.  
  5. Results after Query Execution:
    Expand|Select|Wrap|Line Numbers
    1. Employee_Name    Certification
    2. Flintstone Fred     
    3. Doe Jarl    C++ 3/4/2005;PAER 12/12/2008
    4. Doe Jane    CISSP 1/2/2003;SEC+ 2/3/2004;MCSE 6/23/2007
    5. Doe John    NET+ 4/5/2006
    6.  
  6. Look at the Attachment, and should you have any questions feel free to ask.
Attached Files
File Type: zip Demo.zip (24.9 KB, 1 views)
2 Weeks Ago #4

USTRAGNU1
P: 36
Thank you all for your quick replies! I can't open the demo.zip at work, but I will give this a try, thanks!

Mike
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.