Compare/count line breaks

[richiwatts]

We currently use a filter to show if there is a line break in one cell and not the other. The problem we have is that some cells have 2 line breaks while the column beside it has 1
We need to identify rows that do not have the same amount of line breaks so we can fix them.

This is what we have now. Can anyone provide a query to that will only show rows that do not have the same amount of lime breaks

to select rows containing line breaks: :
[Target] Like "*" & Chr(10) & "*"
 
To show segments where there is a line break in target but not in source:
[Target] Like "*" & Chr(10) & "*" AND [Source] Not Like "*" &
Chr(10) & "*"
 
To show segments where there is a line break in source but not in target:
[Source] Like "*" & Chr(10) & "*" AND [Target] Not Like "*" &
Chr(10) & "*"

---------------------------

 

[dhookom]

You could try find the difference in the length of the values after removing the CHR(10). Something like:

(Len([Source]) - Len(Replace([Source],Chr(10),""))) <> (Len([Target]) - Len(Replace([Target],Chr(10),"")))

Duane
Hook'D on Access
MS Access MVP

 

[richiwatts]

In the tool we work in Source is not editable. Also chr10 can be at the beginning of the cell and/or at the end.
WE need to make sure that Target has the same about of chr10 as the Source.

 

[dhookom]

Did you try what I suggested? Please try before you reply. If you don't understand, ask for clarification.

There is nothing in my suggestion that edits anything. There is nothing in my reply that requires the CHR(10) to be any particular place in the string.

Assume # represents a tab character:
[tt][blue]
Source Target
================= ======================
#abc #xyz##123# #abc #xyz#123#
[/blue][/tt]
Len([Source]) = 15
Len(Replace([Source],Chr(10),"")) = 10
The difference is 5 suggesting 5 characters are #

Len([Target]) = 14
Len(Replace([Target],Chr(10),"")) = 10
The difference is 4 suggesting 4 characters are #

The differences are not the same which means there are more #s in one string than in the other.




Duane
Hook'D on Access
MS Access MVP

 

[richiwatts]

Oh, I did try it but get an error:

There is a syntax error in the SQL-WHERE clause you provided:
Unidentified function 'Replace'in the expression


The last sentense was in Swedish so I am just providing a word for word

 

[dhookom]

If Replace() doesn't work, it suggests you might be using an older version of Access without the patches or possibly not allowing code to run.

Can you confirm this?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

If you have an older version without service packs, you may have to create your own replace function in a standard module like:

Public Function MyReplace(varText As Variant, strFind As String, strReplacement As String) As Variant
    MyReplace = Replace(varText & "", strFind, strReplacement)
End Function

Save the module as "modStringFunctions". Your query might then look like:

SELECT tblRichiWatts.*
FROM tblRichiWatts
WHERE (((Len([Source])-Len(MyReplace([Source],"#","")))<>(Len([Target])-Len(MyReplace([Target],"#","")))));

Duane
Hook'D on Access
MS Access MVP

 

[richiwatts]

We are actually searching in a tool that conencts to an Access datatabse and I suspect it is old. Thsi is from the help file

Note: Desktop Workbench uses Jet SQL from Microsoft® Access. Therefore, the wildcard character is * (not %, as with regular SQL). For more information about the LIKE operator and pattern matching in Jet SQL, see

http://office.microsoft.com/en-us/access/HP010322531033.aspx?pid=CH010410211033

 

[dhookom]

I missed any mention earlier of "a tool that connects". Your tool will determine your solution.

Duane
Hook'D on Access
MS Access MVP