Re: Insert spaces into String

Tech-Archive recommends: Speed Up your PC by fixing your registry

Fred,

Thank you for your feedback. As I indicated to Ken, I am learning code as
problems arise and as I inherit bad data as I did with this. I appreciate
your clear step-by-step instructions.

Sandy


fredg wrote:
I have a data set where all of the spaces have been removed and no common
delimiter except for a capital letter for each new word. For example:
[quoted text clipped - 5 lines]
Thank you for your help.
Sandy

Why would you first remove the spaces and now want to add them back?

Place the following User Defined function in a new Module:

Function PlaceSpaces(FieldIn As String) As String

Dim strNew As String
Dim intX As Integer
Dim intY As Integer

strNew = Left(FieldIn, 1)

For intX = 2 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY >= 65 And intY <= 90 Then
strNew = strNew & Chr(32) & Chr(intY)
Else
strNew = strNew & Chr(intY)
End If
Next intX
PlaceSpaces = strNew

End Function
=========

In a query, add a new column:
NewName:PlaceSpaces([OriginalFieldName])
as criteria on the OriginalFieldName field, write:
Is Not Null

By the way, what do you want to do about names such as
McDonaldBusinessCenter or ABCHomeSupplies, O'BrienMedicalCenter, and
vanderMeerAndSonsSuppyCo.?


--
Sandy

Message posted via http://www.accessmonster.com
.

Relevant Pages

  • RE: search for any lowercase immediately followed by an UPPERCASE
    ... They only process one string. ... Dim strToSearch As String ... Dim strFound1 As String ... Dim strNew As String ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Please, I need a quick help with replace
    ... Function RemoveChar(FieldIn As String) As String ... Dim intX As Integer ... Update YourTable Set YourTable.FieldName = RemoveChar ...
    (microsoft.public.access.formscoding)
  • Re: Problem adding a subform record
    ... To edit, you can still use an InputBox. ... Dim strOld As String, strNew As String, strSQL As String ...
    (microsoft.public.access.forms)
  • RE: How do I output result to table values
    ... For intX = 0 to Ubound ... Now each row of fldValues contains the value to put in your fields: ... Dim rst As Recordset ... If the field data types are not string, you may have to convert the strings ...
    (microsoft.public.access.modulesdaovba)
  • Re: text control
    ... Function RemovePunctuation(StringIn As String) As String ... Dim strNew As String ... Dim intX As Integer ... Dim intY As Integer ...
    (microsoft.public.access.gettingstarted)