Possible to make record list horizontal rather than vertical? 1

[Spyder757]

I’ve got a table that lists question numbers each day. The question numbers are always changing and the number of them varies from 1 to 5 generally.

The data in the query looks as follows:

QUESTION NUMBER
203
304
983
839
738

Now I would like my report to display them as follows:

203, 304, 983, 839, 738

I’ve racked by brain attempting to find a solution to this.

Any suggestions?

Spyder757

 

[scriverb]

Create Function in a database Module:
Public Function Quest_String()
Dim db as DAO.database
Dim rs as DAO.recordset
Dim vQString as string
Set db = CurrentDB
Set rs = db.openrecordset("tblYourTable", dbOpenDynaset)
rs.MoveFirst
Do
vQString = vQString & rs("QUESTION NUMBER&quot & ", "
rs.MoveNext
Loop Until rs.eof
Quest_String = Mid$(vQString,1,Len(vQString)-2)
rs.close
db.close
End Function
Update the name of the table in the Function to your table name.

In the text Control in your report put the following in the Control Source property:
Quest_String()

This will execute the function and display the string of questions. Let me know if this works for you. Bob Scriver

 

[Larsson]

But isn't it easier to just change the report to more than one column, for exampel five.

You do that under page setup for the report.

Larsson

 

[Celeste025]

You can merge fields into one expression fairly easily in the report. Create a text box then in the Control Source Property, type something like this:

=[Field1] & ", " & [Field2] & ", " & [Field3] etc...

 

[scriverb]

No, the numeric value posted in the question is a question number and each record holds a seperate question number. For that reason you have to loop through the records and create a string representation. The values are not all in one record. Bob Scriver

 

[Larsson]

And that's way it's a good idea to have more than one column on the report.

 

[MichaelRed]

As long as the # items remains small, a crosstab query should also work.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scroce]

techi7,

in case you haven't already figured out - try putting

=Quest_String()

in the control source property instead of just

Quest_String()


Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[roselam]

I have the same problem. Columns do not work because the size of the entry in field is always different, it could be 5 letters or 50. Also, it is one field so you cannot concatenate.

 

[scriverb]

roselam, It seems postings are getting a little disjointed here. Just what are you referring to? The original questions was how to string a table of numbers together into a string variable deliminated with commas(,). Now if others are trying to use this to string together text values that is a different story and we would have to look at it different.

Let me know if I can help.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[roselam]

Scriverb, sorry, I am trying to string together text values (from a query) with a comma separating each record. I tried the module you posted above but got a compile error. Can you help? Thanks!!

 

[scriverb]

Sure just post the VBA code. More than likely it is because of the numeric vs text data types.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

I still think the small number of values can be easily obtained from a crosstab query. just use the field as the piviot field - of course there needs to a conditional clause to select / limit the records, but that is necessary anyway. Let litttle old Ms. A. do the walking, queries should be both faster thean code and easier to maintain.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

MichaelRed, I have not used Crosstabs much but if you want to walk us through setting one up as you described I am sure we all would appreciate another faster approach. I have tried a number of times and all I can get is columns and counts of the different values.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MichaelRed]

The Table
QueNum QueText QueSel
131 Where do you land if you go 'over the rainbow'? No
203 Are you over 21? Yes
212 When is now not the correct tims? No
304 Do you reside on OPlante Earth? Yes
414 What is the largest Contiguious land mass? No
738 "Who said the 'The sky is fa;;ing""?" Yes
839 How can apples be Bobs? Yes
983 What is a Coon's age? Yes

the results
QueSel 203 304 738 839 983
Yes 203 304 738 839 983

The query SQL Statement
TRANSFORM Max(tblQues.QueNum) AS MaxOfQueNum
SELECT tblQues.QueSel
FROM tblQues
WHERE (((tblQues.QueSel)=True))
GROUP BY tblQues.QueSel
PIVOT tblQues.QueNum;


So, the sisngle record includes te "horizontal" list (although it is NOT ye olde CSV format, and it of course DOES include the onorous additional field)




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[scriverb]

Thanks for the post. I am going to work with this today to learn all the ins and outs of the crosstab query. Definately has application possibilities.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[CosmoKramer]

Here is another method that might help. It utilizes the records already present in the detail section.

faq703-3587