Find Leading and trailing and middle extra spaces in peoples names

[monicaroman]

Hello,
I need to write a query that tells me who has spaces before their names and after, and when there are two names in the field, who has more than one space.

-The fields are Firstname, MiddleInitial, and LastName

-The data for all of them can look like(using _for spaces):
_Jim_Bob
Jim_Bob__
Jim_Bob
Jim__Bob
_J_Bob__
including in some cases _Jim_Bob_II

I have a query that gives me everyone with a leading or trailing spaces, but also all those with spaces in the middle. For those with spaces in the middle, I only want to know if there are more than one space.

This is the query:
SELECT [Employees].FirstName, [Employees].MiddleInitial, [Employees].LastName
FROM [Employees]
WHERE (((InStr([FirstName]," "))>0)) OR (((InStr([LastName]," "))>0)) OR (((InStr([MiddleInitial]," "))>0)) OR ((([Employees].LastName) Is Null)) OR ((([Employees].FirstName) Is Null))
ORDER BY [Employees].FirstName;


I read a lot about Trim, Mid, Len, Left, Right, etc., and I'm utterly confused, but I also think that I don't need that in this case as I only want to show who has extra spaces.

Please help!
Thanks so much.

 

[jzwp22]

Welcome to the forum!

I'm not sure I understand, you have 3 separate fields but the data in each of those fields have multiple names separated by spaces?

For example, in the firstname field you have multiple first names that are separated by spaces?

 

[monicaroman]

Yes. Exactly. People can have two first names, 2 middle names, 2 last names...I know!

 

[jzwp22]

Regarding the leading and trailing spaces, I would guess that you would want to get rid of them since they are unnecessary (correct?). To do that I would run an update query something along these lines (make a backup copy of the database in case things do not go as planned)


UPDATE Table1 SET Table1.firstname = Trim(firstname), Table1.middlename = Trim(middlename), Table1.lastname = Trim(lastname);



The trim() function will remove all leading and trailing spaces but spaces between non-space text values would be preserved.

I see you used the InStr() function, but it will only return the position # of the first occurrence of the space but not any subsequent spaces. Technically, you could nest InStr() and mid() functions, but it will get quite messy.


If I understand correctly, you want to find those records that have more than 1 embedded space. Once found, then what do you want to do with them?


If your goal is to get rid of extra spaces such that any there will only be one space between successive names within a field, then you may want to explore the Replace() function.

UPDATE Table1 SET Table1.firstname = Replace(firstname," "," "), Table1.middlename = Replace(middlename," "," "), Table1.lastname = Replace(lastname," "," ");

The above query will replace 2 spaces with 1 space. Now if you happen to have 3 spaces between two successive names, the query will reduce two of those 3 spaces to 1 and then you still have the remaining 1 space for a total of two spaces. If you run the same query a second time, you should end up with just 1 space in the end.


Does that help?

 

[MSAccessRookie]

Regarding the leading and trailing spaces, I would guess that you would want to get rid of them since they are unnecessary (correct?). To do that I would run an update query something along these lines (make a backup copy of the database in case things do not go as planned)


UPDATE Table1 SET Table1.firstname = Trim(firstname), Table1.middlename = Trim(middlename), Table1.lastname = Trim(lastname);



The trim() function will remove all leading and trailing spaces but spaces between non-space text values would be preserved.

I see you used the InStr() function, but it will only return the position # of the first occurrence of the space but not any subsequent spaces. Technically, you could nest InStr() and mid() functions, but it will get quite messy.


If I understand correctly, you want to find those records that have more than 1 embedded space. Once found, then what do you want to do with them?


If your goal is to get rid of extra spaces such that any there will only be one space between successive names within a field, then you may want to explore the Replace() function.

UPDATE Table1 SET Table1.firstname = Replace(firstname," "," "), Table1.middlename = Replace(middlename," "," "), Table1.lastname = Replace(lastname," "," ");

The above query will replace 2 spaces with 1 space. Now if you happen to have 3 spaces between two successive names, the query will reduce two of those 3 spaces to 1 and then you still have the remaining 1 space for a total of two spaces. If you run the same query a second time, you should end up with just 1 space in the end.


Does that help?

jzwp22 has a good idea here, but I am not sure that I would want to do an UPDATE as the first step. I would use a SELECT statement to see what the eventual results will be. If I liked them, I could perform the appropriate UPDATE. If I did not not, then I could modify the SELECT until I found a result that I liked. Something like the following might be a good start:

SELECT firstname, Replace(firstname," "," "), middlename, Replace(middlename," "," "), lastname, Replace(lastname," "," ") FROM Table1;
​

 

[monicaroman]

Hello. Thank you so much for your advice.
But it's not what I need.
I don't need to replace or update or trim..this is for someone else who will do something (don't know what) with the query results.
I just need to show the firstname, middleinitial, lastname of those who have leading, trailing and one too many spaces in between the names.
Thanks!!!

 

[MSAccessRookie]

Hello. Thank you so much for your advice.
But it's not what I need.
I don't need to replace or update or trim..this is for someone else who will do something (don't know what) with the query results.
I just need to show the firstname, middleinitial, lastname of those who have leading, trailing and one too many spaces in between the names.
Thanks!!!

Like I said, what I gave you is a good starting point. We can improve on the previous Query by using an IIf() statement to compare the Database contents with the stripped values. This sounds like it might be a little bit closer to what you are looking for.

SELECT IIF(firstname<>Replace(firstname," "," "), firstname + "NOT OK", ""), IIF(middleinitial<>Replace(middleinitial," "," "), middleinitial + "NOT OK", ""), IIF(lastname <>Replace(lastname ," "," "), lastname + "NOT OK", "") FROM Table1;
​

Again, this should be considered a not a finished Query, but instead a starting point that we can always adjust again if my understanding is incorrect.

 

[vbaInet]

You should revise your form to avoid these problems in the future. It's important you have a solid validation routine in place to get rid of these sort of problems before the record is updated or inserted.

I have a query that gives me everyone with a leading or trailing spaces, but also all those with spaces in the middle. For those with spaces in the middle, I only want to know if there are more than one space.

There's no other way except using a function to iterate through every character of the string. The function below will count leading, trailing and middle spaces (greater than 1):

Public Function GetSpacesCount(fieldValue As String) As Long
    Dim i As Long, intLen As Long, intCount As Long
    Dim trimmedVal As String, isEnd As Boolean
    
    trimmedVal = Trim(fieldValue)
    intLen = Len(trimmedVal)
    
    For i = 1 To intLen
        If Mid(trimmedVal, i, 1) = " " Then
            intCount = intCount + 1
        End If
    Next
    
    intLen = Len(fieldValue)
    intCount = IIf(intCount > 1, intCount, 0)
    
    For i = 1 To intLen
        If Mid(fieldValue, i, 1) = " " Then
            intCount = intCount + 1
        Else
            If isEnd = False Then
                isEnd = True
                i = i + Len(trimmedVal) - 1
            End If
        End If
    Next
    
    GetSpacesCount = intCount

End Function

Place this code into a Module (i.e. not a Class Module or a Form Module, just a Module) and the query could be this:

SELECT FirstName, MiddleInitial, LastName, GetSpacesCount([FirstName]  & "") As FirstNameCount, GetSpacesCount([MiddleInitial] & "") As  InitialsCount, GetSpacesCount([LastName] & "") As LastNameCount
FROM [Employees]
WHERE GetSpacesCount([FirstName] & "") > 0 OR  GetSpacesCount([MiddleInitial] & "") > 0  GetSpacesCount([LastName] & "") > 0
ORDER BY FirstName;

I have also put the count as alias fields because you need to know which field is the culprit.

 

[monicaroman]

Thank you so much for all your responses. I'm going with vbaInet's code and will submit it. I liked it because it shows where the spaces are and the number is very visual and will help the person on the receiving end. I'll tweak a little, but I think this works fantastic.
This database was designed and developed by a totally different team, so I'm hoping that I can indeed work on the form at some point, but it's a very formal environment and we need change orders, requests, analysis, etc.

Thanks again everyone!

 

[vbaInet]

Glad we could help!