Query w/ Count of CR&LFs from Comment Fields

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 
0
Utf
2/20/2008 5:05:02 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
718 Views

Similar Articles

[PageSpeed] 7

On Wed, 20 Feb 2008 09:05:02 -0800, MJ <MJ@discussions.microsoft.com> wrote:

>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 [MVP]
0
John
2/20/2008 5:34:44 PM
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  


"John W. Vinson" wrote:

> On Wed, 20 Feb 2008 09:05:02 -0800, MJ <MJ@discussions.microsoft.com> wrote:
> 
> >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 [MVP]
> 
0
Utf
2/20/2008 8:12:01 PM
On Wed, 20 Feb 2008 12:12:01 -0800, MJ <MJ@discussions.microsoft.com> wrote:

>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 [MVP]
0
John
2/20/2008 10:34:20 PM
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
'====================================================


John W. Vinson wrote:
> On Wed, 20 Feb 2008 12:12:01 -0800, MJ <MJ@discussions.microsoft.com> wrote:
> 
>> 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) & "*";
> 
0
John
2/21/2008 12:47:28 AM
On Wed, 20 Feb 2008 19:47:28 -0500, John Spencer <spencer@chpdm.umbc> wrote:

>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.
-- 
             John W. Vinson [MVP]
0
John
2/21/2008 2:50:44 AM
Reply:

Similar Artilces:

Counting Number Records per Unique ID, saving it to another table:
I have a form frmVendors with a subform frmPayments. The main form shows information on vendors, and the subform is a continuous form which lists the checks paid out to each vendor. Users are typically entering new payments into the subform on the main form. The forms are linked to tables: Linked by VendorID (one to many) tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor City tblPayments: Key=CheckID, VendorID, PaymentAmount There is a field in tblVendor called NumberChecks to show the total amount of checks paid out to that vendor. There is a text box on th...

Excel fields too large to work with, any way around this?
Hi, I believe once the Excel cells/fields get too big (individual cel content size) some of the format and find/search/delete functions, macros, and utilities seize to wor become it is somehow truncated and the entire cell doesn't even get processed. How do I go around thi inherent limitation? I'm currently using ASAP Utilities for Excel. What I use most often is the "Advanced Character Removal" Tool under the Text Menu of ASAP. It works great, However this Advanced Character Removal too completely FAILS once the individual field/cell sizes become to large.Some of the ce...

Insert query using date as where condition
I have an insert query that runs every day at midnight that records our client's current environmental status (it's for a mental health facility). Here's the code: ----------------------------------------------------------------------------------------------- INSERT INTO dbo.tblCensusHistoryWithDischarge (FullName, LastName, FirstName, FileNumber, ClientNumber, StaffID, Environment, Location, Residence, FundSource, Act10, StartDate, ExpEndDate, EndDate, CensusTimestamp) SELECT TOP (100) PERCENT dbo.tblClients.LastName + N', ' + dbo.tblClient...

Unprotecting multiple worksheets w/ Macro
I am protecting with a macro, multiple worksheets in a workbook with a macro. I can't get the unprotect of multiple worksheets to work. Does anyone have a working macro to do this. _____________ Tippy Sub UnprotectSheets() Dim sh as Worksheets for each sh in ThisWorkbook.Worksheets if sh.ProtectContents or sh.ProtectScenarios or _ sh.ProtectDrawingObjects then sh.unprotect Password:="ABCD" end if Next End Sub -- Regards, Tom Ogilvy <tippy@att.net> wrote in message news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com... > I am protecting with a macro,...

Remarks field character size
I have been looking at changing the caracter size of the Remarks field on the PO. I have been getting some feed back that I shouldn't do that because its going to make the database unstable. The current size of the Remarks field is set to 30 characters and it just don't cut it for the PO. If we have to add a special instruction we can't. Does any one know anything about the stability issue or how to increase the character size? Regards, -- Microsoft Partner Don't they explain this stuff when you become a "Microsoft Partner"? "Yaniv Zahavi" &l...

SQL Express Install failed installing CRM 4.0 Outlook Client w/off
I have run into this problem a couple times on different laptops. When I try to install the CRM 4.0 Outlook Client, with Offline Access. During the installation process, when it get to the point where it tries to install SQL Server Express, the installation fails. We never did a deployment of CRM3.0, so it is not an upgrade issues, as most of these systems, never had the 3.0 client installed on them. Any ideas where to go from here? Hi, Try to download and install the sql express edition from internet . If it doesnt get installed then it will show you the detailed error . There was pro...

Update Query question 06-26-07
Hello, I am trying to write what I assume would be an update query. I have 2 fields, acct_num and brnch_num in my table. I need to combine these 2 fields into a new field called cust_num. So, if: brnch_num acct_num 123 45678 123 12 12 1 Then I would need the cust_num to read: 12345678, 12300012, 01200001 I'm not sure how to get the zeroes into the cust number so that that number is always 8 diguts. Any help would be greatly appreciated! I wouldn't use an update query for this. Keep the data separat...

Counting 12-28-07
I have a data element in a table named Results. This is defined as a number field. The Values go from 0 to 11. The problem is I need to count a certain value but sometimes that value has not been chosen and that, of course, returns nothing. I would like it to return the result value I am looking for and a '0'. Any ideas? Post your SQL. -- KARL DEWEY Build a little - Test a little "Bunky" wrote: > I have a data element in a table named Results. This is defined as a number > field. The Values go from 0 to 11. > The problem is I need to count a certai...

Query Not Returning Correct Amount of Records
I have 6 tables that I have built 6 different queries on. Individually, these queries return the correct amount of records in the tables. Which is 80 records in all tables involved. Then I built a query that collects data from the 6 queries and this query returns 75 records. What am I doing wrong? Thanks Don As a guess, your query is not correctly structured for the data you have. For instance, if one of the 6 tables does not have a matching record that could cause you to "lose" a record or two in the query if you have set up a join to that table and have n...

how do I get the FROM field to not show
I have a user who is using Outlook 2003 on XP Pro. when she starts a new email she has: From: To: CC: Subject: How do I get rid of the "From:" field? She has no idea how she turned it on and I can't see how to turn it off.... Thanks Kelvin "Kelvin" <me@mydomain.com> wrote in message news:elzCUTEpKHA.1552@TK2MSFTNGP05.phx.gbl... >I have a user who is using Outlook 2003 on XP Pro. > when she starts a new email she has: > From: > To: > CC: > Subject: > > How do I get rid of the "From:" field? If...

How to get one field to update based on a selection from a drop do
New to Access. I want to create a form that coworkers can use to lookup information in a database for inputting data into a PO as well as submit new records into the database. I want to have a control that is a combo drop down box where the can select a row from a list of items in a specified field and based on that selection it will update the below text box control with the corresponding data in the field next to it on the database. Make sense? Basically need to figure out how to update one control based on data selected from the drop down box control above it??? Private Sub...

Add fields of Scroll window in VBA ?
Hi, I am able to add other fields in form but I am unable to add fields of scrolling window in VBA. Thanks in advance -- Aditya While I'm happy you have things solved, I don't believe what you have below is the correct solution. You should be able to add any fields of any scrolling window to your project. I'm darn sure that is the case because I've used VBA to pull values from lookup windows before and those are of course browse only. My only note for scrolling windows is you have to make sure the window is "filled" before you try to add it to vba. ...

Query based Subform will not allow editing
I have a maintenance DB that has energy lockout points associated with pieces of equipment so we can safely do maintenance. I have an 'edit existing lockout' form, based on a query, that lists in a subform the various points required for a certain piece of equipment. My system used to work, but now I can edit the main, but the subform has locked me out. All I get is a doorbell tone when I attempt to enter info. I can enter the req'd info in the tables, but but my form went snafu. Any ideas? Thanks in advance Does your query allow editing? Check the asterisk in the record...

Need help with SQL Query
Our database consists of over 10,000 items. I'd like to re-organize and format alot of the items we have in our database. For instance, I'd like to do something simple like search all items in the database with a description of "7 1/8" and replace it with "718" and so on. Is there a site or a point of reference to refer to for using basic SQL commands for use with RMS? Another example of where I could use SQL would be for assigning select dept's/cats/items for a certain sale promotion instead of having to goto the properties of each item and manually selecti...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

Add something to "To:" message field
Hi is there any way how to add something to sender's name field? I need to add organisation name before the sender's name (to distinguish different organisations)? The only way I found so far is changing users name in Active Directory and this is (for me :-) not preferred way as I have to change all 500 mailboxes' names :-( Any hints? On Wed, 8 Dec 2004 07:49:10 -0800, Jiri Kopacek <JiriKopacek@discussions.microsoft.com> wrote: >Hi is there any way how to add something to sender's name field? I need to >add organisation name before the sender's name (to d...

textbox query
I am using a userform to have data transfered to a sheet. I currently use the code below to send it to a worksheet but what I would like to have happen is for it to add the total of the textbox to the total already in the cell. R = 3 'or whatever desired row variable Sheets(2).Cells(R, 5).Value = TextBox4.Text Any help would be great Thanks in advance Greg With Sheets(2).Cells(R, 5) .Value = .Value + CDbl(TextBox4.Text) End With -- HTH Bob Phillips "Greg B" <laptopgb@ihug.com.au> wrote in message news:d7pjmh$ch3$1@lust.ihug.co.nz... > I am using a userfo...

export query with formulas to excel
Is there a way to export my Access query to Excel 2003 while keeping the formulas in Excel. For example, in my Access query I have a field called "Other" which is defined as the following: Other: [DiffTotal]-[BudTo] [DiffTotal] and [BudTo] are "real" fields in the query and become columns in Excel. Instead of having the value of "Other" appear in Excel, I'd like the "Other" column to have the formula where, for example in row 1, it could look like "=A3-A2." Thanks, ~Mark When Access exports to Excel, it exports the...

Pivot Table Field Header
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hello, <br> I'd like to find out if there's a way to HIDE the FIELD HEADERS from Pivot Tables. <br> I can't find it in the Pivot Table Options which seems like the place it would be found. <br><br>Thanks in advance. ...

Customize field in My Task
Dear all, In My task view in PWA, is it possible to customize the name of a Field. For example, i want field "Text1" to be called "Test". Thanks for your help Yann -- When you create the custom enterprise field, you need to give it a new name, which you apparently did not do. Log into PWA with administrator permissions and then click Server Settings - Enterprise Custom Field Definition. Click the name of the field in question to open it for editing. Give the field a new name and then click the Save button. From your description of the problem, it so...

Identifying Modified Database Fields
Situation is as follows: - We have an SQL Server database table that is being updated in a synchronisation process from another source database, via code that loops through the source records, finds the corresponding records in the destination database, and updates relevent fields (via RDO, SELECT corresponding record, Edit, change all fields, Update.) This is existing code. There is a field in the destination database (ModifiedDate) that should only be updated if any of the destination fields have changed as a result of the synchronisation. The question relates to how th...

Display a count of unique records in a query in Access 2007
Hi, I'm not sure if I should be in this group, or Reports, but I'll start here. I have a user who would like to do a count of unique records and display the information in a query, or a report. Here are the basics: She has a list of donors, some of the donors donate more than once to the same cause. What she would like is a list of the number of donors, per cause. But, if the donor ID repeats for the same cause, she does not want that ID counted again. Example: We can get a query to return a count of the records per cause. It is returning a value of "7" for a cause,...

How to change the AutoNumbers inID field in my finished database?
This may be a simple question but... I have already entered in my data and want to change the autonumbers, to a different way I have set it up. Is this possible? On Mon, 4 Feb 2008 19:48:01 -0800, Jako <Jako@discussions.microsoft.com> wrote: Define "different". -Tom. >This may be a simple question but... >I have already entered in my data and want to change the autonumbers, to a >different way I have set it up. Is this possible? Jako <Jako@discussions.microsoft.com> wrote: >I have already entered in my data and want to change the autonumbers, to a ...

Count Outlook messages.
Hi there I'm pretty new at Outlook 2000 so i'm not too sure weather you can do this or not but i'll try to explain it as best as i can. I'm getting messages from a specific email sender address. And what I want to do is count how many messages i am getting from this sender. I am on a network and i think we are running Windows 2000 server with Exchange 2000. I have a Win2k PC with Outlook 2000. Is there a way to count the amount of messages that i'm getting from the sender or do i need a specific type of software to do it? Thanks guys. Tell me if you need anymore ...

add custom fields after redeploy
I redeployed a MSCRM install to a virtual environment. The redeploy worked fine, but I can't add custom fields. I get an error saying that the Publisher is not the original publisher for CRM. I had to manually create the distributor and the publications. Any help is appreciated. this is the answer i think ============ appears this is caused by replication not being setup correctly =========================== You have redeployed Microsoft Business Solutions CRM 1.2 by using the Redeployment Tool. You try to add a new custom field to Microsoft CRM by using the Schema Manager in Micro...