Make a continuous list from a field within a table

[Slot]

Apologies for asking here but I've trawled the web for the last 2 hours and can't find what I'm looking for.



Plus go easy on me I'm a novice when it comes to access.

I have a table - lets call it TableEmployee

Within that table I have a number of fields, for example

Name
Payroll
Department

In total ther are over 300 employees within the table with all three fields populated for each.

I then have a Query - again lets call it QryFilter. This enables me to search and filter out only those that are from a particular department. IE I will search on workshop and it pulls back 100 records.

Now what I would like to do is from the query is produce a string, either in another table or within a text box, where all the payroll numbers from the results of the search are combined into a continuous string with a coma between each. (don't require any of the other fileds, just payroll numbers)

IE the end result I am looking for something like this

AG1785,AD6835,GH6895,JK8976,FG6795 and so on for the 100 odd records that it will pull back

(just to put it into context I then need to copy and paste the string produced and drop into into some other software)

Again, apologies if this has been covered a 1,000 times before but I'm struggling

[jgelpi16]

You may be better off doing this in VBA. Here is an example. I have not verified this for syntax, but should help out.

What this will do is take each Payroll Number from your Query and dump it into a text box on a form. I would put this code in a click event of a button.

Dim db as Database
Dim rs as Recordset
Dim vPayrollNum as String

Set db = CurrentDb
Set rs = db.OpenRecordset("QryFilter", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
     vPayrollNum = vPayrollNum & ", " & rs("Payroll")
     rs.MoveNext
Loop

txtResults.Value = vPayrollNum '--------> txtResults is the name I gave the text box, it can be whatever you want
rs.Close

Let me know if you have any questions.

[Slot]

Many thanks. Potentially it looks like its working.

I say potentially as I get a run time error - 3163 "The field is too small to accept the amount of data you attempted to add. Try inserying or pasting less data"

In total the 'list' I'm attemptoing to pull over should be 80 'payroll's in number, each payroll being 10 or 11 digits.

I tried all the dumb things us novices try. Increasing the box size, hunting through properties for something which limits the numbers etc.

I did filter out alot of the results and retested it with just 5 records and they popluated the text box perfectly so it would appear that size does matter. Any ideas - managed to find something which said there was a 100 character limit on it but not sure if that sounds right

[Slot]

Forget it, just redid the text box and works perfectly now - Thanks for helping me out, greatly appreciated, owe you a beer

[jgelpi16]

Didn't realize you were working with such a large population. I apologize. Let's try another method. Create a table with a field in it and set the field to Memo instead of Text. Then run the below code instead of the txtRests.Value = vPayrollNum.

Dim mySQL as string

mySQL = "INSERT INTO tableName (FieldToInsertValuesTo) VALUES (" & vPayrollNum & ");"
CurrentDb.Execute mySQL, dbFailOnError

IF your payroll numbers are formatted as text instead of number you'll need to use the below statement:

mySQL = "INSERT INTO tableName (FieldToInsertValuesTo) VALUES ('" & vPayrollNum & "');"

Notice the ' in the VALUES portion before and after the vPayrollNum portion. This designates the value as numeric where as the first one with the absence of the ' declares the value as a number.

[ssanfu]

FYI, there are two other options are available.

1) Loop thru the recordset, writing to a text file. (I use this a lot)

2) Use the "TransferText Action". You can set up an export specification to transfer the data to a text file.

[thhui]

It seems that this is another form of regex exercise in access query result.