Display Table Elements as a Paragraph with Numbering?

[DHavokD]

So I have a query that takes 4 differents fields of an entry and combines them into a block of text (with some nice formatting of course).

Now for the report a bunch of these blocks of texts need to be combined and numbered. So if the table was:

Bob House Cat
Jim Apartment Dog
Betty House Hamster

The paragraph would be:
1. Bob: House; Cat; 2. Jim: Apartment; Dog; 3. Betty: House, Hamster;



So the two things I cannot figure out:
1. Numbering them as the query outputs them.
2. Make it so its a run on paragraph.

So right now I have:
Bob: House; Cat;
Jim: Apartment; Dog;
Betty: House; Hamster;

Note: If it matters, the data comes from a query and this is a sub-report of a main report. And this data is completely random made up stuff for the purpose of example.

[RuralGuy]

Maybe this link will be helpful.

[DHavokD]

This appears to do what I have already done. I already wrote the SQL into the query to combine the four fields of each record. The problem is numbering the records as they pop out on the report, and not having them line separated.

[RuralGuy]

If you are using a UDF then you can number them in the function. Is your UDF putting in the vbCrLf?

[DHavokD]

I dont have a CrLF anywhere, this is the code that defines the field in my query:

Update: "::" & Consolidated.Title & ":: " & IIf((Consolidated.Description<>""),("DESCRIPTION: " & Consolidated.Description & "; "),"") & IIf((Consolidated.[Significant Events - Past]<>""),("SIG EVENTS PAST: " & Consolidated.[Significant Events - Past] & "; "),"") & IIf((Consolidated.[Significant Events - Future]<>""),("SIG EVENTS FUTURE: " & Consolidated.[Significant Events - Future] & "; "),"") & IIf((Consolidated.Description<>"") And (Consolidated.[Significant Events - Past]<>"") And (Consolidated.[Significant Events - Future]<>""),"","No Significant Events; ")

Basically puts those four fields together. Would it be possible to add numbering at this point? Cause a query add new autonumbers?

[RuralGuy]

You have a field named *Update*?? Are you aware that that is a reserved word? That is one heck-of-an IIF structure. I would still do it as a UDF just for readability and maintainability.

[DHavokD]

Okay changed it from update... But still, how would I number the entries? Either at query level or as they are popped into the report...

And dont you know terri-bad code = job security.

[RuralGuy]

The code I pointed to is a UDF that does all of the work but it looks like your fields are all on one record, is that correct? The UDF I pointed to works when the fields are on different records.

[DHavokD]

Yes, all the fields are in one record. I just need to figure out how to number the records, can you add autonumbering in a query?

[RuralGuy]

I get the feeling you are not real comfortable with VBA code. It also looks like the numbering you want is with the fields concantenation on one record and not with numbering each record in the RecordSet. A UDF would accomplish this and I would be glad to assist you in creating the UDF.

[RuralGuy]

In studying your nested IIF statements, they do not return a string in the form you described in your first post. There is added text to the string such as "SIG EVENTS FUTURE: ".

[DHavokD]

Ya I later added that, just as a way for me to say what each block of text was. So I get as reference my original, it actually does this:

::Billy:: RESIDENCE: House; PET: Cat; (etc)

[RuralGuy]

This should give you something to work with for your UDF.
Warning <<< AIR CODE >>>

Function MyString(InDescription As String, _
                  InPast As String, _
                  InFuture As String) As String
   Dim MyCount As Integer
   MyCount = 0
   ' Receive up to 3 strings in the following form:
   ' Bob: House; Cat;
   ' Jim: Apartment; Dog;
   ' Betty: House; Hamster;
   ' Return them in one string in the following form:
   ' 1. Bob: House; Cat; 2. Jim: Apartment; Dog; 3. Betty: House, Hamster;
   ' In our case it is invoked in a query this way:
'      YourField = MyString(Consolidated.Description, _
'                           Consolidated.[Significant Events - Past], _
'                           Consolidated.[Significant Events - Future])
   If Len(InDescription & "") > 0 Then
      MyString = "1. DESCRIPTION: " & InDescription
      MyCount = 1
   End If
   If Len(InPast & "") > 0 Then
      If Len(MyString & "") > 0 Then
         MyString = MyString & " 2. SIG EVENTS PAST: " & InPast
      Else
         MyString = "1. SIG EVENTS PAST: " & InPast
      End If
      MyCount = MyCount + 1
   End If
   If Len(InFuture & "") > 0 Then
      Select Case MyCount
         Case 0
            '-- Nothing so far
            MyString = "1. SIG EVENTS FUTURE: " & InFuture
         Case 1
            '-- Only one parameter so far
            MyString = MyString & " 2. SIG EVENTS FUTURE: " & InFuture
         Case 2
            '-- Got both prior parameters
            MyString = MyString & " 3. SIG EVENTS FUTURE: " & InFuture
      End Select
   Else
      If MyCount = 0 Then
         '-- We need to return at least something
         MyString = "No Significant Events; "
      End If
   End If
End Function

[DHavokD]

Ah you are thinking of different counting than i meant.

Lets say the four fields of each record are combined to make a "paragraph" of that record. Right now the query that does this turns my sample set of data into 25 "paragraphs". What I am trying to do is have the "paragraphs" themselves numbered:

1. Paragraph1
2. Paragraph2
3. Paragraph3
...
25. Paragraph5

I am thinking the easiest way would be to have the query itself attach a number to each paragraph it outputs (some autonumbering possible in a query?).

I am at home till Tuesday, which means on a mac and without my database or Access to mess around with. So if you got any suggestions by Tues would be awesome.

[RuralGuy]

Oh! Maybe what you are looking for is one of Stephan Lebans' routines.