Replacing space with dot

[JeroenMioch]

Hi people,

I have an initials field called Letters.
Users have to input customers initials in this field.
It annoys the hell out of me that some of the users are not educated enough to put dots behind each initial.
So, i have to enforce this programaticly.



An example how people input the field :

A
AB
A. B

of course i want these inputs to be :

A.
A.B.

Can anyone point me in the right direction ?

Thanks !

[ketbdnetbp]

JeroenMioch -

Search Access Help/Visual Basic Reference for String Manipulation Keywords and Replace function.

All the best,


Jim

[June7]

These are first and last name initials?

Build a complex function to handle every possible situation and replace the corrected value in table.

Alternatively, don't even ask users to input customer initials. You can extract the first letter of each name part programatically, assuming the name parts are in separate fields.

Left([Firstname,1) & "." & Left(Lastname,1) & "."

[JeroenMioch]

June the initials are first names only.
And where i live the firstname is not allways the same as the initials.

For example someone is called G.M.A. wenthworth but his calling name can be Joseph.
Dont ask me why though..
Besides that, their full first names are not in my database.


@KetdbNetdb :

Ill try that, but usualy the help files dont make sence to me. Thats why i search the internet for usable examples and edit them to my situation.

Both thanks for the reply though !

[June7]

Can you get the first names? Have fields for formal, legal name and build initials from those parts. Then have a field for 'nickname'.

However, on review, the code might not be so bad after all. Something like:

Function FixInitials (x As String) As String
x = Replace(x, " ", "") 'remove all spaces from the string
x = Replace(x, ".", "") 'remove all periods from the string
'now rebuild the initials string
aryI = Split(x)
x = ""
For i = 0 to UBound(aryI) - 1
x = x & aryI(i) & "."
Next
FixInitials = x
End Function

Then call the function in a query or textbox or in VBA:
FixInitials(Me!Initials)

[JeroenMioch]

This is the way i would approach it myself. Its just i m not that great of a coder to write modules.

Ive tried running the module upon leaving the letters (initials) field. But unfortunately it doesnt do anything.
And im getting an error upon exiting the form (inproper use of NULLs)

any thoughts ?

[rpeare]

I think I would tend to prevent them from entering the data in the first place rather than trying to correct it for them so in the BEFORE UPDATE event or the ON EXIT event or similar place you could have:

if instr([Initials], " ") >0 OR instr([Initials], ".") > 0 Then
  Msgbox "INITIALS FIELD CAN NOT CONTAINS SPACES OR PERIODS (.).  PLEASE RE-ENTER INITIALS"
endif

Then you can use june's function to clean up your existing data by creating a new module and running it independently of your form as a one time deal.

[June7]

Educating users can be frustrating but that is one option for you. So slap their wrists often enough and eventually they get the message. Probably want to adapt rpeare's wrist slap code to deal with all non-alpha characters (accidental entry of comma or /).

Or let them stay ignorant and just fix whatever they enter. Again, might want to deal with all non-alpha characters.

Consider this version of function to exclude all non-alpha characters:

Function FixInitials (x As String) As String
Dim aryI As Variant
aryI = Split(x)
x = ""
For i = 0 to UBound(aryI) - 1
   If Chr(UCase(aryI(i))) > 64 And Chr(UCase(aryI(i))) < 91
      x = x & aryI(i) & "."
   End If
Next
FixInitials = UCase(x)
End Function

My proposed code does not change the data saved to table unless you run the function in an UPDATE sql action.

If you want the data corrected immediately, then run code in textbox AfterUpdate event.

If Not IsNull(Me.textbox) Then
Me.textbox = FixInitials(Me.textbox)
End If

[JeroenMioch]

Hey guys,

Thanks for the input sofar.
June you forgot "then" behind If Chr(UCase(aryI(i))) > 64 And Chr(UCase(aryI(i))) < 91 but i got that corrected.
Then i got an error "type mismatch" but after the first time i didnt see that again.
And last but certainly not least; When i enter the initials and go to another field the field becomes empty hahaha.
I could have done that myself :

Private Sub letters_AfterUpdate()
If Not IsNull(Me.Letters) Then
Me.Letters = "" 
End If
End Sub

Ok that was just kidding offcourse
Any idea whats wrong here ?

@ Rpeare : I do want the period characters behind each character, but not the spaces

[rpeare]

As far as preventing them from entering a bad set of initials you can use this in the AFTER UPDATE of the INITIALS field

Dim i As Integer
Dim sInputString As String
Dim sOutputString As String

sInputString = Replace([TestString], " ", "")
sInputString = Replace([sInputString], ".", "")

sOutputString = ""
For i = 1 To Len(sInputString)
    sOutputString = sOutputString & Ucase(Mid(sInputString, i, 1)) & "."
Next

you can do the test to see if they actually entered it correctly, and depending on whether or not you want to bug them, you can put in a 'I had to correct this field for you dummy' type message in there as well, it's more code or you can silently fix it like this code does. Of course this assumes that spaces and periods are the only problem. You could also test to see if the characters other than space and period are between a and z but you get the idea.

[JeroenMioch]

Trying to educate them is a waist of time to be honest.
Automating it is the way to go for me.

I tried you code on the afterupdate field of the Letters (Dutch for Initials) field but it doesnt do anything as far as i can see.

Ill post the complet code below just to make sure that im not missing something :

Private Sub letters_AfterUpdate()
Dim i As Integer
Dim sInputString As String
Dim sOutputString As String
sInputString = Replace([sInputString], ".", "")
sOutputString = ""
For i = 1 To Len(sInputString)
    sOutputString = sOutputString & UCase(Mid(sInputString, i, 1)) & "."
Next
End Sub

Should this work ?

[rpeare]

no, it won't

My code is stripping out all periods (.) and spaces, your code is trying to do something with a null string

you start doing things with sinputstring but if you don't give it a value it's basically just "". You have to tell the code what sinputstring is before you start working on it.

Private Sub letters_AfterUpdate() 
Dim i As Integer 
Dim sInputString As String 
Dim sOutputString As String 

sInputString = Replace([Letters], " ", "")
sInputString = Replace([sInputString], ".", "") 
sOutputString = "" 
For i = 1 To Len(sInputString)     
    sOutputString = sOutputString & UCase(Mid(sInputString, i, 1)) & "." 
Next 

Letters = sOutputString
End Sub

[June7]

Ooops, glad you caught the missing 'Then'. Obviously, I was typing code into post and not testing it in VBA.

As you can see, more than one way to get something done. Some may be more efficient and robust than others.

Do you want to handle possibility of other non-alpha characters than dots and spaces?

[JeroenMioch]

Rpeare that works like a charm !

Thanks you both for the input.
I now never have to annoy myself about my less then educated coworkers

Cheers guys !

[JeroenMioch]

Little note though.
I got an improper use of NULL error when exiting my form with my custom made close button.
Cant reproduce the error but i was just wondering what the code below has to do with the problem that was just solved :

Private Sub KNPformClose_Click()
On Error GoTo Err_KNPformClose_Click
DoCmd.Close
DoCmd.OpenForm "frmSchakelbord"
Exit_KNPformClose_Click:
    Exit Sub
Err_KNPformClose_Click:
    MsgBox Err.Description
    Resume Exit_KNPformClose_Click
End Sub