Splitting text box text into sentences

[Danick]

I have a continuous form with multiple text boxes based on a query. These is a cmd button that passes the current record in the form to a report. What I would like to do is parse (or split) the text in one of those text boxes called [Discrepancies] into multiple rows each deliminated by a period. Basically, just trying start each row in that text box with a new sentence.

Are there any examples of how to do that?

 

[theDBguy]

Depending on what's in the textbox, you might be able to simply use the Replace() function.

 

[ebs17]

Replace(AnyText, ".", "." & Chr(13) & Chr(10))

 

[Danick]

Depending on what's in the textbox, you might be able to simply use the Replace() function.

Replace(AnyText, ".", "." & Chr(13) & Chr(10))

That sound interesting. How do I call that up in the report?

 

[theDBguy]

That sound interesting. How do I call that up in the report?

You can try using it either as the Control Source or a calculated column in your query that you use for your report.

 

[Danick]

You can try using it either as the Control Source or a calculated column in your query that you use for your report.

OK - I think I got it. Basically just created a column in my query with the replace code and then call that new field in the report. Seems to work and very simple.

But found a problem with doing it this way. The query shows all the text properly split into sentences, but the report truncates the text box at 255 characters

 

[Danick]

Replace(AnyText, ".", "." & Chr(13) & Chr(10))

This code truncates the text box in the report to 255 characters. When I switch back to the original field, the field is not truncated. I don't understand why it does this. But is there some other code that does the same thing but doesn't truncate the field?

 

[CJ_London]

Try using the replace function as the controlsource to your control instead of as a column in a query

 

[The_Doc_Man]

The field is truncated at 255 characters because (if you count the characters after replacement) you exceeded 255 characters by replacing dots with dots and two more characters. That number, 255, is the absolute limit of characters in a SHORT TEXT field. To allow for longer fields you have to go to LONG TEXT, but that might run into problems since there are some things LONG TEXT won't do that SHORT TEXT will do, particularly related to searches and indexes.

It also wouldn't hurt to check on the field modified by that REPLACE function to see if you got strings of dots (.....) because of repetitive replacement. Shouldn't happen, but worth checking.

 

[Danick]

Try using the replace function as the controlsource to your control instead of as a column in a query

Not sure how to do that. So right now the report has a textbox and the controlsource is the name of that field. How can I use the replace function in the control source? I tried using the same VBA with an = in front of the VBA above but just getting "#Type!" in the report.

 

[Danick]

The field is truncated at 255 characters because (if you count the characters after replacement) you exceeded 255 characters by replacing dots with dots and two more characters. That number, 255, is the absolute limit of characters in a SHORT TEXT field. To allow for longer fields you have to go to LONG TEXT, but that might run into problems since there are some things LONG TEXT won't do that SHORT TEXT will do, particularly related to searches and indexes.

It also wouldn't hurt to check on the field modified by that REPLACE function to see if you got strings of dots (.....) because of repetitive replacement. Shouldn't happen, but worth checking.

The field is set to a long text. It used to have this problem before when trying to use the group function in the query. Allen Brown showed that this happens and suggested to remove the grouping. So I did that and all is well. But now it's doing the same thing again with this Replace code.

 

[theDBguy]

The field is set to a long text. It used to have this problem before when trying to use the group function in the query. Allen Brown showed that this happens and suggested to remove the grouping. So I did that and all is well. But now it's doing the same thing again with this Replace code.

Just curious, how do you know it's getting truncated? Can you add a new column in the query using the Len() function?

 

[Danick]

Just curious, how do you know it's getting truncated? Can you add a new column in the query using the Len() function?

When I use the original field in the report, the whole text from the query is displayed in the text box in the report but there are no line breaks. When I use the Replace function, the text box in the report displays the text with line breaks, but only up to 255 characters.

 

[theDBguy]

When I use the original field in the report, the whole text from the query is displayed in the text box in the report but there are no line breaks. When I use the Replace function, the text box in the report displays the text with line breaks, but only up to 255 characters.

Maybe you can post a screenshot? How did you know it cuts off at 255 chars? Did you use the Len() function?

 

[arnelgp]

if your [Discrepancies] field have abbreviations on them and ended with a dot (.), it will also be split into a new sentence which you don't want to happen.

 

[The_Doc_Man]

See this linked article (which itself contains some links.)

Redirecting

 

[ebs17]

Long text means that up to about one gigabyte of text can be stored, even if controls in forms and reports can only display the first 64,000 characters.
Therefore, long text (Memo) is stored internally differently than short text (Text(255)) and is used and operated differently.

Applying Replace in a query results in a calculated field. Access/Jet itself sets the data type for such a calculated field, and this is Text(255) by default. There is no switch here to freely select something else.

You can often get around this indirectly by using the following:
AnyText => Left(AnyText, 65535)

 

[Danick]

Long text means that up to about one gigabyte of text can be stored, even if controls in forms and reports can only display the first 64,000 characters.
Therefore, long text (Memo) is stored internally differently than short text (Text(255)) and is used and operated differently.

Applying Replace in a query results in a calculated field. Access/Jet itself sets the data type for such a calculated field, and this is Text(255) by default. There is no switch here to freely select something else.

You can often get around this indirectly by using the following:
AnyText => Left(AnyText, 65535)

Can't find a way of using

Replace(AnyText, ".", "." & Chr(13) & Chr(10))

In combination with

AnyText => Left(AnyText, 65535)

To get around the truncation issue with calculated fields.

 

[ebs17]

Sub test_datatypes()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT AnyText AS F1, Replace(AnyText, '.', '.' & Chr(13) & Chr(10)) AS F2," & _
        " Left(Replace(AnyText, '.', '.' & Chr(13) & Chr(10)), 65335) As F3 FROM YourTable")
    With rs
        Debug.Print 1, .Fields("F1"), .Fields("F1").Type
        Debug.Print
        Debug.Print 2, .Fields("F2"), .Fields("F2").Type
        Debug.Print
        Debug.Print 3, .Fields("F3"), .Fields("F3").Type
        .Close
    End With
End Sub

Try this.
10 => dbText
12 => dbMemo

 

[Danick]

Sub test_datatypes()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT AnyText AS F1, Replace(AnyText, '.', '.' & Chr(13) & Chr(10)) AS F2," & _
        " Left(Replace(AnyText, '.', '.' & Chr(13) & Chr(10)), 65335) As F3 FROM YourTable")
    With rs
        Debug.Print 1, .Fields("F1"), .Fields("F1").Type
        Debug.Print
        Debug.Print 2, .Fields("F2"), .Fields("F2").Type
        Debug.Print
        Debug.Print 3, .Fields("F3"), .Fields("F3").Type
        .Close
    End With
End Sub

Try this.
10 => dbText
12 => dbMemo

I'd really like to try to see if this code works but have no idea how or where to put it.

The Form with the cmd button opens a report with the record based on a primary field. Do I put this code in the click event of the cmd button? The Open event of the report? Should it be a function in its own module and call it up somehow in the report? Seems like this is getting to be more complicated than I was anticipating. I can't believe no one has ever asked for something like this this but I looked and can't find a sample database with a similar request.