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

Help Writing Field Names to a String

P: 16
I am trying to concatenate field names (separated by a comma or some of the delimiter) and write them to record in my database, or store them as a string that I can use in an expression later on. So far I have code that pulls all the field names from a query and writes them to the debug window. But I am stuck after that. I can't figure out how to get the output into another format. Any suggestions would be appreciated.


Expand|Select|Wrap|Line Numbers
  1. Sub Fieldnames()
  2.     Dim Rst As Recordset
  3.     Dim db As Database
  4.     Dim f As Field
  5.     Dim qdfParmQry As QueryDef
  6.         Set db = CurrentDb()
  7.         Set qdfParmQry = db.QueryDefs("qry_1test")
  8.         qdfParmQry("Forms!Occu_formatting!Species") = [Forms]![Occu_formatting]![Species]
  9.         Set Rst = qdfParmQry.OpenRecordset()
  10.     For Each f In Rst.Fields
  11.     Debug.Print f.Name
  12.     Next
  13.     Rst.Close
  14. End Sub
Mar 13 '14 #1

✓ answered by Seth Schrock

You would need a string variable to store the field names.
Expand|Select|Wrap|Line Numbers
  1. Dim strFields As String
Now, just below your Debug.Print line, put this
Expand|Select|Wrap|Line Numbers
  1. strFields = strFields & f.Name & ", "
This will leave you with a comma and a space at then end of the string when you are done, so after your loop, you will probably want to drop those.
Expand|Select|Wrap|Line Numbers
  1. strFields = Left(strFields, Len(strFields) - 2)
You might have to play with the number that you are subtracting to make sure it works, but otherwise it should work.

Share this Question
Share on Google+
3 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
You would need a string variable to store the field names.
Expand|Select|Wrap|Line Numbers
  1. Dim strFields As String
Now, just below your Debug.Print line, put this
Expand|Select|Wrap|Line Numbers
  1. strFields = strFields & f.Name & ", "
This will leave you with a comma and a space at then end of the string when you are done, so after your loop, you will probably want to drop those.
Expand|Select|Wrap|Line Numbers
  1. strFields = Left(strFields, Len(strFields) - 2)
You might have to play with the number that you are subtracting to make sure it works, but otherwise it should work.
Mar 13 '14 #2

P: 16
That worked, thanks!
Mar 13 '14 #3

NeoPa
Expert Mod 15k+
P: 31,768
That works Seth, but an alternative approach is to use :
Expand|Select|Wrap|Line Numbers
  1. strFields = strFields & ", " & f.Name
This has the benefit of being easier to tidy up afterwards using :
Expand|Select|Wrap|Line Numbers
  1. strFields = Mid(strFields, 3)
Mar 14 '14 #4

Post your reply

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