Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


how to strip characters ?

Posted on 2003-02-25
Medium Priority
Last Modified: 2012-06-22
How do i fix the function below so it gets called only if there are vbCr or vbCrLf ?

Currently it gets called all the time...

Any suggestions...

Public Function removeChars(s As String) As String
  Dim str   As String
  Dim i     As Integer
  For i = 1 To Len(s)
    Select Case Mid(s, i, 1)
      Case vbCr, vbLf
        str = str & vbCrLf
      Case Else
        str = str & Mid(s, i, 1)
    End Select
  removeChars = str
End Function

Private Sub STR_NOTES_BeforeUpdate(Cancel As Integer)
Me.STR_NOTES = removeChars(Me.STR_NOTES)
'cleanit (Me.STR_NOTES)
End Sub
Question by:sdesar

Expert Comment

ID: 8022027

You might want to try to search for this character first vbCR or vbCRLF.  Try using InStr function.  It will return the position of the string found.  If 0, then not found, else you've got it.  When you've found the vbCR or vbCRLF character, then call the RemoveChars function.

Have a go at it.



Accepted Solution

gregdaly earned 80 total points
ID: 8022044
If I read your note correctly you want to modify Private Sub STR_NOTES_BeforeUpdate so that it calls removeChars only if STR_NOTES contains either vbCr or vbCrLf or both.

The specific ANSI character code for Cr is Chr(13) and Lf is Chr(10).  You want to test the STR_NOTES for the presence of these characters using the INSTR function.  INSTR returns 0 if the search string is not found.

Here's how I would write it:

Private Sub STR_NOTES_BeforeUpdate(Cancel As Integer)
Dim vstr As String
vstr = me.STR_NOTES
If InStr(1, vstr, Chr(13)) > 0 Or InStr(1, vstr, Chr(10)) > 0 Then
    vstr = removeChars(vstr)
    me.STR_NOTES = vstr
End If
End Sub

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question