Remove ' from field 2

[Ajwebb]

Hi all,

Have asked this question before but have had a bit of trouble wording it, so bare with me please.

I have a form with several fields on, Surname, Forename and Learn_id.

When I enter a surname and forename the names are concatenated and placed into my Learn_id field.

e.g.

Anthony Webb

would be WebbA in the Learn_id field. Unfortunately to navigate around my databases i have several command buttons which use stlinkcriteria code.

When a name such as James o'connor is typed in my Learn_id becomes O'coJ and when i click my command button, it thinks that the learn_id is O.

Is there anyway that when i concatenate my fields to the Learn_id that is only takes the letters from the surname, instead of any character that is in the text box.

Have been told to use the replace function but am unsure fully of how to use it and where to put it.

Also there may be different characters in a name such as the ~ key and - and ' so i am unsure of what to do.

Thanks for any help given.

Regards

Anthony

 

[SgtJarrow]

So what you are saying, is that you want a Learn_id field that is equal to the lastname without any special characters and the first initial.

Copy this into a module and save it as whatever:

' ***************** Start Code ****************
Public Function DeleteEmbedded(CharsToDelete As String, FromString As String)
Dim i As Integer, s As String

For i = 1 To Len(FromString)
If InStr(CharsToDelete, Mid$(FromString, i, 1)) = 0 Then
s = s & Mid$(FromString, i, 1)
End If
Next i
DeleteEmbedded = s
End Function
' **************** End Code ***********************

Next, wherever you are creating the Learn_id, it should now be:

Learn_id = DeleteEmbedded("'-~", [lastname]) & Left([firstname], 1)

You can add or remove characters from the list to be stripped out as necessary....let me know if you have any problems.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[DeanWilliams]

Hi,

You could use the Ascii character codes for this, so you can restrict Learn_ID's to alpha characters only.

You can use the replace function if you have Access2000 and above. I don't think it appears in earlier versions. I could be wrong. There is a faq with code to mimic the Replace function on Tek-Tips. Do a search.

However using the replace function will be a bit fiddly when trying to remove lots of different chars so use this code as the condition to concatenate, in the function that creates the Learn_ID:

If (Asc(strChar) >= 65 And Asc(strChar) <= 90) Or (Asc(strChar) >= 97 And Asc(strChar) <= 122) Then

' Concatenate to Learn_ID

End If

If you need any more help let me know.

Hope that helps,

Dean

 

[RemyS]

Hi Robert,
You code was just the answer I was looking for, well almost...

I tweaked it a bit to replace a character rather than delete it (replacing it with an empty string would do the same)

Thanks.
Remy

Public Function ReplaceEmbedded(CharsToRemove As String, CharsToReplace As String, FromString As String)
'''------------------------------------------------
''' Replaces one character with another in a string
'''------------------------------------------------
        Dim i As Integer, s As String
        
        For i = 1 To Len(FromString)
            If InStr(CharsToRemove, Mid$(FromString, i, 1)) = 0 Then
                s = s & Mid$(FromString, i, 1)
            Else
                s = s & CharsToReplace
            End If
        Next i
        ReplaceEmbedded = s
End Function

Hundreds of ways to do things with PC's, and learning new ways every day.

 

[SgtJarrow]

As long as you worked it out.....glad to be of help.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com