Query w/ Count of CR&LFs from Comment Fields

[MJ]

This may not be as complex as it seems to me, but I am trying to determine
how much of a problem I may have in one of my databases.

The core of the problem starts when my users cut & paste email responses
into the comment fields that contain CR & LFs. It would not be a big deal
except that these comment fields get output to a text file for uploading to
another database external to mine and it does not handle the CR & LFs.

My predecessor wrote code to strip out CR & LF combinations:

If (Not IsNull(Me.Comment#) Then
Me.Comment#= Replace([Comment#, Chr(13) & Chr(10), " ")
End If

but I am still running into CR & LFs within the comment fields after this
code runs.

I need to determine how many CRs + LFs [ chr(13) & chr(10) ] are remaining
within two comment fields of my database. I want to produce a list
containing the Record Numbers (RecordNum) plus a count of the number of times
[ chr(13) & chr(10) ] occcurs within the Comment* fields

(TblConcern) RecordNum Comment1 Comment2
001
002
003
...

EXAMPLE
OUTPUT RecordNum CntCmnt1 CntCmnt2
005 1 1
020 2
103 3
...

1) How best to write a query to give me this assessment tool?

2) Is my predecessor's code the most correct code to take care of the CR &
LF issue within these comment fields? If not, what would be a better code?

Thanks in advance,
MJ

 

[John W. Vinson]

This may not be as complex as it seems to me, but I am trying to determine
how much of a problem I may have in one of my databases.

The core of the problem starts when my users cut & paste email responses
into the comment fields that contain CR & LFs. It would not be a big deal
except that these comment fields get output to a text file for uploading to
another database external to mine and it does not handle the CR & LFs.

My predecessor wrote code to strip out CR & LF combinations:

If (Not IsNull(Me.Comment#) Then
Me.Comment#= Replace([Comment#, Chr(13) & Chr(10), " ")
End If

I hope this was a typo - shouldn't there be a closing bracket after Comment#?
For what it's worth, I would avoid using the date delimiter # in any
fieldname!

but I am still running into CR & LFs within the comment fields after this
code runs.

That's VERY odd. Try backing up your database for safety and then running an
Update query

UPDATE yourtable SET [Comment1] = Replace([Comment1], Chr(13) & Chr(10), " ")
WHERE [Comment1] LIKE "*" & Chr(13) & Chr(10)

and similarly for Comment2.

I need to determine how many CRs + LFs [ chr(13) & chr(10) ] are remaining
within two comment fields of my database. I want to produce a list
containing the Record Numbers (RecordNum) plus a count of the number of times
[ chr(13) & chr(10) ] occcurs within the Comment* fields

(TblConcern) RecordNum Comment1 Comment2
001
002
003
...

EXAMPLE
OUTPUT RecordNum CntCmnt1 CntCmnt2
005 1 1
020 2
103 3
...

1) How best to write a query to give me this assessment tool?

Don't search it - fix it. See above.

2) Is my predecessor's code the most correct code to take care of the CR &
LF issue within these comment fields? If not, what would be a better code?

It looks like that code will replace data as the VBA code is being executed -
but you didn't provide the context. The code will NOT replace crlf's already
in the table unless the records are brought into the code somehow.

 

[MJ]

John,

In answer to your question about the close Paren, yes was a typo, oops. As
for using the date delimiter "#", I was not using it as a date delimiter, but
I do understand that it could have been confused, my bad.

I tried your Update query but get 0 updates, as written. In one of the
comment fields, I could believe that there are NO CR & LF combinations, but
in the other I know that there at least several and since the table is 75K+
records I do not want to go thru this manually.

If I change the WHERE portion from your suggestion to:

... WHERE (Comment1) Is Not Null;

I get a very large percentage of the records in both cases being changed.
The above change basically forces the UPDATE query to make the change to
nearly all records, but I do not want to do that either since I will have
reset the update flags on all of these resords and send all of this data to
the output files to update that external database.

I DO AGREE with your statement, "Don't search it - fix it", but in this case
I would like to find out how big a problem this really is before I say blast
it all or say that it is acceptable as is. Do you have any other suggestions?

Thanks again,
MJ

This may not be as complex as it seems to me, but I am trying to determine
how much of a problem I may have in one of my databases.

The core of the problem starts when my users cut & paste email responses
into the comment fields that contain CR & LFs. It would not be a big deal
except that these comment fields get output to a text file for uploading to
another database external to mine and it does not handle the CR & LFs.

My predecessor wrote code to strip out CR & LF combinations:

If (Not IsNull(Me.Comment#) Then
Me.Comment#= Replace([Comment#, Chr(13) & Chr(10), " ")
End If

I hope this was a typo - shouldn't there be a closing bracket after Comment#?
For what it's worth, I would avoid using the date delimiter # in any
fieldname!

but I am still running into CR & LFs within the comment fields after this
code runs.

That's VERY odd. Try backing up your database for safety and then running an
Update query

UPDATE yourtable SET [Comment1] = Replace([Comment1], Chr(13) & Chr(10), " ")
WHERE [Comment1] LIKE "*" & Chr(13) & Chr(10)

and similarly for Comment2.

I need to determine how many CRs + LFs [ chr(13) & chr(10) ] are remaining
within two comment fields of my database. I want to produce a list
containing the Record Numbers (RecordNum) plus a count of the number of times
[ chr(13) & chr(10) ] occcurs within the Comment* fields

(TblConcern) RecordNum Comment1 Comment2
001
002
003
...

EXAMPLE
OUTPUT RecordNum CntCmnt1 CntCmnt2
005 1 1
020 2
103 3
...

1) How best to write a query to give me this assessment tool?

Don't search it - fix it. See above.

2) Is my predecessor's code the most correct code to take care of the CR &
LF issue within these comment fields? If not, what would be a better code?

It looks like that code will replace data as the VBA code is being executed -
but you didn't provide the context. The code will NOT replace crlf's already
in the table unless the records are brought into the code somehow.

 

[John W. Vinson]

I tried your Update query but get 0 updates, as written. In one of the
comment fields, I could believe that there are NO CR & LF combinations, but
in the other I know that there at least several and since the table is 75K+
records I do not want to go thru this manually.

Very odd. Could you post the actual SQL of the query that you are using?

If I change the WHERE portion from your suggestion to:

... WHERE (Comment1) Is Not Null;
I get a very large percentage of the records in both cases being changed.

Welll... sure, but if the comment doesn't in fact contain a crlf, it will just
"change the value to itself". The database will bloat (possibly substantially)
and should be compacted!

The above change basically forces the UPDATE query to make the change to
nearly all records, but I do not want to do that either since I will have
reset the update flags on all of these resords and send all of this data to
the output files to update that external database.

Now that I simply don't understand at all. You haven't mentioned external
databases or update flags.

I DO AGREE with your statement, "Don't search it - fix it", but in this case
I would like to find out how big a problem this really is before I say blast
it all or say that it is acceptable as is. Do you have any other suggestions?

The Update query should work *for updating a local or linked Access table*. It
appears that is not what you wish to do. Could you explain your update
process, and what this output file might be???

That said... to get the count,

SELECT tblConcern.RecordNo, Len([Comment1]) - Len(Replace([Comment1], Chr(13)
& Chr(10)) / 2 AS CRLFS1, Len([Comment2]) - Len(Replace([Comment2], Chr(13) &
Chr(10)) / 2 AS CRLFS2
FROM tblConcern
WHERE Comment1 LIKE "*" & Chr(13) & Chr(10) & "*"
OR Comment2 LIKE "*" & Chr(13) & Chr(10) & "*";

 

[John Spencer]

Pardon me, but I think the where clause should have read
WHERE [Comment1] LIKE "*" & Chr(13) & Chr(10) & "*"

Note the inclusion of a wild card character at the end.

UPDATE yourtable
SET [Comment1] = Replace([Comment1], Chr(13) & Chr(10), " ")
WHERE [Comment1] LIKE "*" & Chr(13) & Chr(10) & "*"

Or like the following if you are not using a .mdb or .mde database


UPDATE yourtable
SET [Comment1] = Replace([Comment1], Chr(13) & Chr(10), " ")
WHERE [Comment1] LIKE '%' & Chr(13) & Chr(10) & '%'



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I tried your Update query but get 0 updates, as written. In one of the
comment fields, I could believe that there are NO CR & LF combinations, but
in the other I know that there at least several and since the table is 75K+
records I do not want to go thru this manually.

Very odd. Could you post the actual SQL of the query that you are using?

If I change the WHERE portion from your suggestion to:

... WHERE (Comment1) Is Not Null;
I get a very large percentage of the records in both cases being changed.

Welll... sure, but if the comment doesn't in fact contain a crlf, it will just
"change the value to itself". The database will bloat (possibly substantially)
and should be compacted!

The above change basically forces the UPDATE query to make the change to
nearly all records, but I do not want to do that either since I will have
reset the update flags on all of these resords and send all of this data to
the output files to update that external database.

Now that I simply don't understand at all. You haven't mentioned external
databases or update flags.

I DO AGREE with your statement, "Don't search it - fix it", but in this case
I would like to find out how big a problem this really is before I say blast
it all or say that it is acceptable as is. Do you have any other suggestions?

The Update query should work *for updating a local or linked Access table*. It
appears that is not what you wish to do. Could you explain your update
process, and what this output file might be???

That said... to get the count,

SELECT tblConcern.RecordNo, Len([Comment1]) - Len(Replace([Comment1], Chr(13)
& Chr(10)) / 2 AS CRLFS1, Len([Comment2]) - Len(Replace([Comment2], Chr(13) &
Chr(10)) / 2 AS CRLFS2
FROM tblConcern
WHERE Comment1 LIKE "*" & Chr(13) & Chr(10) & "*"
OR Comment2 LIKE "*" & Chr(13) & Chr(10) & "*";

 

[John W. Vinson]

Pardon me, but I think the where clause should have read
WHERE [Comment1] LIKE "*" & Chr(13) & Chr(10) & "*"

Thanks, John! You're absolutely right - my criterion would just find crlf's at
the very end of the string, not embedded.