Formatting a list from table in paragraph/sentence structure

[dillonhh]

Hi All,

I have not been able to find this anywhere else so here goes:

Let's say I have one table of Providers and child table of Amenities that each provider provides. I am trying to create a report that lists each provider and their dozen or so amenities from the child table, however I would prefer that each prover's list not take up this long, narrow space down each page. Instead, I would like to list the providers and then somehow find a way to list the amenities in a nice paragraph style each separated by comma's going across the page.

Is this possible, it seems so simple but I am having trouble finding the answer.

Thanks a lot for the help, Dillon

 

[liddlem]

Hi Dillonhh
Have you tried using a crosstab query to generate your data source? This might provide a solution?

Also, I think that there might be a way to read each value into a single variable before being written into the report, but I dont know how.

Perhaps the guru's could provide some source code?

 

[dillonhh]

Thanks liddlem,

I will look into that.

In the mean time, if there are any guru's out there reading, feel free to comment!

D

 

[DCrake]

Simple Software Solutions

HI

Ok Here how to do it.

First Declare an array TmpArray(xxx) 'Where xxx is the maximum amount of items possible in the list.

Next Dim a counter

Dim iCnt as Integer

Next loop through all the amenities that are linked to the parent record

For x = 0 to xxx

If Amenity = "" Then Exit For

TmpArray(x) = Amenity
iCnt = x

Next x

Now we start again using the above array

Dim SmartString as String


For x = 0 to iCnt -1

SmartString = SmartString & ", "

Next

So what we have done now is to loop through all the amenities (excluding the last on and placed a comma and a space between them).

Next we need to add " and " to the end of the SmartString and the last item in the array

SmartString = SmartString & " and " & TmpArray(iCnt)

Finally, Just in case any items in the array were blank we can remove all empty items

SmartString = Trim(Replace(SmartString,", , ",", "))

You can now use the SmartString to parse into your report

Code Master:

 

[dillonhh]

Thanks a lot for the reply,

Private Sub Report_Load()

Dim TmpArray(100) As String
Dim SmartString As String
Dim i As Integer
Dim iCnt As Integer

For i = 1 To 100
If AmenityID = "" Then Exit For
TmpArray(i) = DLookup("Amenity", "Amenities", "AmenityID=" & AmenityID)
iCnt = i
Next

For i = 1 To iCnt - 1
SmartString = SmartString & TmpArray(i) & ", "
Next

SmartString = SmartString & " and " & TmpArray(iCnt)
SmartString = Trim(Replace(SmartString, ", , ", ", "))

Is this above code correct then?

I am still not sure how to use the SmartString. I tried using a text box and setting it =SmartString, but i get no where. This may be a very easy answer. I do not, afterall, have much experience making very nice reports.

Thanks again for the help,
Dillon

 

[dillonhh]

Would anyone at all be willing to help me with this? The above code just reproduces the last item in the list 100 times rather than cycle through the list of records. I want to set a text box equal to this SmartString so display the information.

The report is set up right now to have a main report with the hotel info and then a subreport with tables called amenities, activities, etc. So each subreport comes from a child table connected to the hotel parent table.

Please help!!!

 

[dillonhh]

screen shot or current report, and then the pdf is how i would like it to end up more or less

 

[KeithG]

In your code you look up the same ID 100 times try adding 1 to the id in the loop or better yet use a recordset.