Strip out unwanted characters from memo field 2

[Hfnet]

I have a linked table which has a memo field. The field is taken from an Approach database. At the end of every line there appears a ì symbol, and a square where there is a carriage return.

How can I strip out the ì values when I show the memo field on an Access form (and make the sqaures a new line)? I am guessing with maybe a module or something, but am unsure.

Thanks

 

[AccessGuruCarl]

Take a look at InStr, MidStr, Len Function.

You will need to setup a loop to look for those characters. Get the length so you know where to begin on the next loop, Then return ever thing less those characters into a temp string. Add a new line, then loop again.

Hope this helps...
If you need a sample of what I'm explaining let me know!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Hfnet]

I get the basic idea, but a sample would help a lot, thanks

 

[AccessGuruCarl]

Hfnet,

I'm assuming that the symbol | your referring to is the pipe symbol. If so, this code should do the trick!
If not look at the Character Set or Asc Function to determine the symbol.

This code goes on your form.
Edit memMemo for your Memobox name in all 3 locations.

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Dim strMemo As String       'The controls value
Dim strChrSearch As String  'Character to find
Dim intFoundChr As Integer  'Gets the found position within the string
    strChrSearch = Chr(124) 'Pipe symbol
    
If Not IsNull(Me.memMemo.value) Then
    strMemo = Me.memMemo.value
    intFoundChr = InStr(1, strMemo, strChrSearch)
    If intFoundChr > 0 Then   'Remove symbols
        Me.memMemo.value = fRemoveSymbols(strMemo, strChrSearch)
    End If
End If
 
'Any other code you have to process here
Exit_Form_Current:
Exit Sub
Err_Form_Current:
MsgBox "Critical Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Form_Current
End Sub

This code paste into a new module, save it as Remove PipeSymbols

Option Compare Database
Option Explicit

Function fRemoveSymbols(strMemo, strChrSearch As String) As String
On Error Resume Next            'Required if used on the current event
Dim strNewString As String      'The controls new value
Dim intLength As Integer        'The passed strings length
Dim intStartPos As Integer      'Sets a starting position within the string
Dim intEndPos As Integer        'Sets the found position within the string
Dim intNumChrReturn As Integer  'Returns Number of characters in specfic string
  intLength = Len(strMemo)
  intStartPos = 1
  intEndPos = InStr(intStartPos, strMemo, strChrSearch)
'Add 1st line, Use Trim to remove any leading/trailing spaces
strNewString = Trim(Mid(strMemo, intStartPos, (intEndPos - 1)))
Do Until intEndPos = intLength
'Set new starting point for search
  intStartPos = intEndPos + 2   '+2 for Pipe and Carriage Return
'Find the next character in the string.
  intEndPos = InStr(intStartPos, strMemo, strChrSearch)
    If intEndPos = 0 Then   'No more symbols found, We must be at the end!
        intEndPos = intLength   'Set exit point
    Else
        'Get the length of the next line in characters
        intNumChrReturn = intEndPos - intStartPos
        'Add the next line to the current string
        'Use Trim to remove any leading/trailing spaces
        strNewString = strNewString & vbCr & Trim(Mid(strMemo, intStartPos, intNumChrReturn))
    End If
Loop
fRemoveSymbols = strNewString
End Function

Good Luck

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Hfnet]

I am taking the data from a table which has been imported from Approach. The | symbol is not correct. The charcaters appear when data moves to the next line, and where there is an enter (new line).

I have spoken with a guy who says there are 3 or 2 characters combined to produce the weird character, so i need to look for 3, then 2 to filter out the correct values.

 

[AccessGuruCarl]

Hfnet,

You can change Chr(124) to Chr(13) the carriage return, then change the start end positions. But if you leave it on the current event, it would return true 'always' and want to remove the symbols.

You'll need to figure out one of those other characters.
Post a partial string of the data imported, include a couple of lines if you can't figure out the character value.

I'll see if I can figure out the ascii value your getting.


AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[TomCologne]

Also see:

ACC2000: Sample Function to Replace or to Remove Special Characters in an MDB

http://support.microsoft.com/default.aspx?scid=kb;en-us;210433&Product=acc2000

or

ACC2000: How to Strip Specific Characters from a String

http://support.microsoft.com/default.aspx?scid=kb;en-us;210227

TomCologne

 

[Hfnet]

Thanks for replies, here is some detail of some lines of text...

Please read notes below, Î
diagnose the problem with the machine and then let me know beforeÎ


Student has damaged the Î
Jack lead plug? & also the catches on the lid are loose. Please Î
advise damage when you know.


The symbols reflect the end of lines in the approach memo field, not carriage returns, i don't think.

 

[TomCologne]

These characters are most likely causing your problem:

Carriage Return = Chr(13)
Line Feed = Chr(10)
Tab = Chr(9)

To test for presence of any of these characters, put a simple expression like

InStr([YourMemoField],Chr(13))

in a query to get the position of the first occurence in the string.

If this doesn't work, result=0, try to determine the character with

Asc(Mid([YourMemoField],InStr([YourMemoField],",")+2,1))

This should return the character number for the "Please read notes below, Î" part.

Once you know the character, you can adjust AccessGuruCarl's or Microsoft's function to remove or replace it.

TomCologne

 

[AccessGuruCarl]

Hfnet,

On the forms code change

strChrSearch = Chr(124) 'Pipe symbol

to

strChrSearch = Chr(206) ' Î

Based on your sample, you may want to remove the carriage return when adding the next line. The memo box will display the data a little better, if it's a narrow memobox.

In the module, in the If statement, change

strNewString = strNewString & vbCr & Trim(Mid(strMemo, intStartPos, intNumChrReturn))

to

strNewString = strNewString & " " & Trim(Mid(strMemo, intStartPos, intNumChrReturn))




AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Hfnet]

Thanks to everyone who assisted here, I now have fully populated memo fields with no funny characters!!! )

 

[PHV]

You may also consider the Replace function, if ac2k or above.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hfnet]

Hmm, having said that, when I compiled an mde of the database, the funny characters returned!!

 

[gunalan]

Hi,

(I have already read the heading called strip out unwanted characters from memo field)

how do that for text field.

I am beginner in access and i have little basic understanding of VB.

So could u find way to remove unwanted characters from text field.

thank you....

 

[AccessGuruCarl]

gunalan,

A memobox is the same as a textbox for general terms except their is no limit on the amount of characters allowed...

Which basically means any code you find to strip characters from a memo field will also work on a text field.

There are several examples you can follow from within this post!

Good Luck
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[gunalan]

hi,

being a beginnar...

how to run above code...

they have given one code for form..

other code for removing pipe symbols.....

basically i am looking for removing symbol "["...

i guess it is possible through above code through small changes....

could you give me idea...

TITLE
De tribunis aerariis disputatio, Jo. Nic. Madvigii Madvig, J. N. (Johan Nikolai), 1804-1886 Hauniae, Typis Directoris J.H. Schultz. Aulae et Universitatis Typographi, 1838

[3;21_ber die Heimat Pseudoisidors Lurz, Georg M[1;115_nchen, Dr. H. L[1;115_neburg, Verlag, 1898

i have taken it from source...

u will be see title contains..[ and square..

pls give give clear picture..as i have little idea how to do it...

thank you...

 

[AccessGuruCarl]

Use the code I supplied in the 4th posting from the top.

Change strChrSearch = Chr(124)
To strChrSearch = Chr(91)

Change Me.memMemo.value
memMemo to your textbox name in all the locations
Their are 3 total...

In the Function...

You may need to change the second ....
'Set new starting point for search
intStartPos = intEndPos + 2 '+2 for [ and Carriage Return

To...
'Set new starting point for search
intStartPos = intEndPos + 1 '+1 for [

That should take care of it...







AccessGuruCarl
Programmers helping programmers
you can't find a better site.