Controlling Field output in Reports 2

[bdavid50]

This may be difficult to explain or I may be approaching the whole issue wrong. I am developing a small database the has multiple numbers in a single field seperated by a comma. The field will be populated from a form. The following example demonstrates what the input data will look like: 25648, 28964-01, 89745, etc., not every entry has the same set of numbers or are the number of digits consistent. When I run the report using a query that contains this field I would like the report output to look like this

Number

25648,
28964-01,
89745,

In other words I would like the report to create a new line each time it detects a comma. But instead my output looks like this

Number

25648, 28964-01, 89745,


Is there a way for the report to look at the query results and put a break to a new line each time a comma (or any other delimiter) is used, so that the desired results is achieved.

I hope this is clear enough
Thank you for your help

bdavid50
bdavid50@aol.com

 

[JerryDennison]

What you really need to do is normalize the data. Having more than one record in a single field goes against the First Normal Form("...All attributes must be atomic...&quot. I would strongly recommend that you break this field out into a subtable/subform.

 

[bdavid50]

Thank you for your input, I thought that, that was really the way to go about the whole issue. I am just an intermedite user with Access and was trying to make this work. I appreciate your input.

 

[BoxHead]

bdavid50,

Jerry is right. Still, your question got me to wondering.

Is there a way for the report to look at the query results and put a break to a new line each time a comma (or any other delimiter) is used, so that the desired results is achieved.

This is what I came up with. Maybe it will help reduce the amount of re-entry required.

There are nine text boxes and one command button on an unbound form.
text01 contains the long string with different values separated by commas.
The click event on the command button "SeparateString" populates text1 through text8
as necessary with the individual values from text01.



Private Sub SeparateString_Click()
Dim Counter, charCount, stopCount As Integer
Dim strValue, charValue As String
stopCount = Len(Text01) + 1
Counter = 1
charCount = 1
strValue = Text01

Do
If Mid(strValue, charCount, 1) = "," Then
Me("text" & Counter) = Trim(Left(strValue, charCount - 1))
strValue = Mid(strValue, charCount + 2, stopCount)
Counter = Counter + 1
charCount = 1
Else
charCount = charCount + 1
End If
Loop While Len(strValue) > 0


End Sub


BoxHead

 

[bdavid50]

Thank you for your input, I agreed with Jerry and am in the process of reworking my form. I currently have no records and so rework is not that big of a chore.

 

[JimRich]

Try running a query that gets the data from your string a piece at a time. Use a string function to pull out each piece based on the position of a "space". Then base your report on the query.