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

A concatenate question in reverse

P: 3
I have a query that pulls the right info but I am tring to display it in a form

The data looks like this

autonumber usernumber userfirstname userlastname checkbox
0000 0001 john smith false
0001 0001 john smith false
0002 0001 john smith true
0003 0002 sally jones true
0004 0002 sally jones true
0005 0002 sally jones false

I would like it displayed like this

usernumber userfirstname userlastname checkbox autonumber
0001 john smith false 0000, 0001
0001 john smith true 0002
0002 sally brown false 0005
0002 sally brown true 0003, 0004

I have all but the autonumber set up correctly. I am a bit new to the visual basic part of Access any help will be welcomed.
May 3 '10 #1

✓ answered by TheSmileyCoder

No it would not. It would however allow you a easy way to display such items in a subform, and you could even tie events to the subform, so that when a user clicks a payroll, that payroll gets opened.

Now if you need it comma delimited, you would need to write a function for that, as far as I know.

It could go something like this.

Again I would base the form on your user table, and then add a custom function in a textbox, this ensures that the function will not be called for all your users, but only for those you are actually displaying in the form.

Expand|Select|Wrap|Line Numbers
  1. Private Function fStrPayrolls(UserNumber as long) as string
  2.   Dim rsPR as DAO.Recordset
  3.   Dim strSQL as string
  4.   strSQL="SELECT * FROM tbl_Payroll WHERE usernumber=" & UserNumber & " ORDER BY autonumber"
  5.   set rsPR=CurrentDB.OpenRecordSet(strSQL,dbopendynaset,dbReadOnly)
  6.   Dim strResult as string
  7.   do while not rsPR.EOF
  8.     strResult=strResult & "," & rsPR!Autonumber
  9.     rsPR.MoveNext
  10.   Loop
  11.   fStrPayrolls=strResult
  12.  
  13. End Function

Share this Question
Share on Google+
7 Replies


dsatino
100+
P: 393
you can't do this with a single query
May 3 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Try to explain what you need, because it sounds like you might not have setup your tables properly. Depending on your needs, I would likely suggest using a subform,but I need to know more about what you need this for.
May 3 '10 #3

dsatino
100+
P: 393
I would agree, but this isn't my question. I'm new to this today so I probably responded in the wrong place somehow. Given your expert status, I'd love it if you could look at my question regarding how to use a custom function as both a filter and a non-filter. Thanks!
May 3 '10 #4

P: 3
@TheSmileyOne
Ok, I have everything but the last part of this query setup and I am trying to list the series of autonumbers 0000,0001,0002,0003 That is attached to a specific user This is for a payroll form and report to show payout to the listed autonumbers
May 3 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
What is your table structure? Do you have a Table of users and a seperate table of payrolls? Are they related by the UserNumber?

If so, what you likely need to do, is first setup a form, tied to user table, then insert a subform (simplest approach is to drag the payroll table into the user form, while in design view, and then setup the Parent/child relation between the 2 forms).
May 3 '10 #6

P: 3
@TheSmileyOne
this will not help list a single column from mydatabase to a comma delimited list in a textbox on my form
May 3 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
No it would not. It would however allow you a easy way to display such items in a subform, and you could even tie events to the subform, so that when a user clicks a payroll, that payroll gets opened.

Now if you need it comma delimited, you would need to write a function for that, as far as I know.

It could go something like this.

Again I would base the form on your user table, and then add a custom function in a textbox, this ensures that the function will not be called for all your users, but only for those you are actually displaying in the form.

Expand|Select|Wrap|Line Numbers
  1. Private Function fStrPayrolls(UserNumber as long) as string
  2.   Dim rsPR as DAO.Recordset
  3.   Dim strSQL as string
  4.   strSQL="SELECT * FROM tbl_Payroll WHERE usernumber=" & UserNumber & " ORDER BY autonumber"
  5.   set rsPR=CurrentDB.OpenRecordSet(strSQL,dbopendynaset,dbReadOnly)
  6.   Dim strResult as string
  7.   do while not rsPR.EOF
  8.     strResult=strResult & "," & rsPR!Autonumber
  9.     rsPR.MoveNext
  10.   Loop
  11.   fStrPayrolls=strResult
  12.  
  13. End Function
May 3 '10 #8

Post your reply

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