Remove blank spaces after strings in fields

[Modify_inc]

I finally got my fields or columns setup the way I need them so I can successfully import an Excel Range.

Thankfully it imported with no errors, though right away I noticed there were many blanks spaces after the First Name, Last Name, Address, and City.
I checked in Excel and sure enough extra spaces are included in all the above fields.

Well I was going to show you an example, but apparently this forum automatically removes extra spaces, how coincidental, just what I'm looking for



So I tried to use the function Trim in Excel, but it was annoyingly troublesome with so many rows and columns, plus it didn't correct the actual columns, but instead displayed them in a new columns.

So I thought maybe it would be wiser to have Access remove the spaces. Of course with my limited knowledge of Access, I went to Google.

I found this script that looks like what I need exactly, I just don't know how to execute or apply it to my database, specifically to those four fields.

I have it as a file, trmStringExample.mdb and also in text format as I have shown below.

Any Suggestions?


The Code:

Attribute VB_Name = "modTrimSpaces"
Option Compare Database
Option Explicit

'Created 2006
'Function removes spaces in front, back and middle of string

Public Function trimSpaces(strInput)
Dim strCharRemove As String 'assigns the char to look for & replace
Dim strCurChar As String 'Current character to match up
Dim i As Integer 'count of chars in string
Dim lngSpaceCount As Long 'tracks the count of spaces

strCharRemove = " " 'looks for a space (can be any character)

'trim spaces between text
For i = 1 To Len(strInput)
strCurChar = Mid(strInput, i, 1) 'get the character count of string

If InStr(strCharRemove, strCurChar) = 0 Then 'if the character matches a space
trimSpaces = trimSpaces & strCurChar 'create parsed string
lngSpaceCount = 0 'reset to zero
Else
If lngSpaceCount < 1 Then 'if the space count is already 1, do nothing
trimSpaces = trimSpaces & strCurChar 'else create parsed string
lngSpaceCount = lngSpaceCount + 1 'add 1 to the space count so the space is not repeated
Else
lngSpaceCount = lngSpaceCount + 1 'track count of spaces
End If
End If
Next i

'trim spaces in front & in back of text
trimSpaces = Trim(trimSpaces)
End Function

[Modify_inc]

I experimented and put the code in the Visual Basic Editor. I'm pretty sure I done it correctly, as it it showing under Modules as trimSpaces. I saved it and restarted the database, but now I get and error when I open the Contacts table: There was an error compiling this function. The Visual Basic module contains a syntax error. Check the Code, and then recompile it.

Of course I have know idea what the correct syntax should be.

Any Suggestions

[John_G]

Hi -

If all you need to do is remove leading and trailing spaces from text fields in a table, all you need is an update query with the trim function. Add to the query all the fields that need fixing, then in the "Update To ", put trim([fieldname]). For example, the Update To for City would contain Trim([City]), with similar syntax for the other fields. Run the query and that's all there is to it. No code needed.

Note that the code you show removes ALL spaces - not what you wanted.

HTH

John

[Modify_inc]

Hi -

If all you need to do is remove leading and trailing spaces from text fields in a table, all you need is an update query with the trim function. Add to the query all the fields that need fixing, then in the "Update To ", put trim([fieldname]). For example, the Update To for City would contain Trim([City]), with similar syntax for the other fields. Run the query and that's all there is to it. No code needed.

Note that the code you show removes ALL spaces - not what you wanted.

HTH

John

I got it all setup and and ran it. I had to confirm that I wanted to update all 375 records. I did, then I got this error:

Contacts can't update all the records in the update query.
Contacts didn't update 0 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 3 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?

I'm just wanting to remove extra spaces, I don't understand why I'm getting this technical error. From the looks of it, the only issue is with 3 records due to validation rule violations.
Of course I don't know what 3 of my 375 records it could be referring to, and why only those 3 are in violation.

Any suggestions?

Update: ha I think I just figured it out, there were exactly 3 records missing some of the search criteria I had set up, that being the first and last name.

Thanks for the suggestion, worked quickly and effectively, and I learned something new!

[John_G]

Glad we could help.

When you get that error, where it indicated a validation rule violation, it is one of two things- either you are attempting to add a record which would result in a duplicate on a unique index (not necessarily the PK), or referential integrity in a defined relationship is being violated.

Sounds like you got it fixed.

Cheers!

John

[Modify_inc]

Glad we could help.

When you get that error, where it indicated a validation rule violation, it is one of two things- either you are attempting to add a record which would result in a duplicate on a unique index (not necessarily the PK), or referential integrity in a defined relationship is being violated.

Sounds like you got it fixed.

Cheers!

John

Thanks for the clarification!

I was attempting to add the Middle Initial to the Contact Name and it appears to work but I ran into another problem with extra spaces or so I thought.

I thought that if there is no spaces in a field, then wouldn't that be considered null? When I put my mouse in the field, there is not a character or space to delete, but if I hit delete anyways, my expression works correctly.

If I try to run the Trim function on the Middle Initial field, I get 150 record(s) due to validation rule violations. So that is telling me that Access thinks those fields are already blank which they are when looking at them and clicking in them. Yet my expression thinks otherwise, unless I hit the delete key in one of the supposedly blank null fields, then it works.

So this really confuses me, how do I determine that a field is completely null, and how do I make sure it is, so that my expression will work correctly.

My expression:

IIf(IsNull([LastName]),IIf(IsNull([FirstName]),[Company],[FirstName]),IIf(IsNull([FirstName]),[LastName],[FirstName] & (" "+[Middle]+". ") & " " & [LastName]))

Thanks again
Mike

[John_G]

Hi -

When entering data in a form, entering only blanks in a field, or deleting the contents results in a Null for the field; there is no way of entering a zero-length string into a control, unless you use VBA.
In table design view, check the setting of Allow Zero Length for the Middle Initial; unless you changed it, it probably is set to NO, meaning the zero-length string - "" - is not allowed. It's important to note that the zero-length string is not Null.

Now, what's happening to generate all those errors is that the Trim function, when applied to an all-blank (or zero-length) field coming from Excel, results in in a zero-length string, which is not valid.

Here's what you can try:

Either change the Allow Zero Length property to Yes, so at least you can get all the data in without errors, or
Modify your (update?) query to replace blank strings with nulls: iif(len(trim([Initial])) = 0, Null, [Initial]) so that you will have no zero-length strings.

The second option might be better, as your null tests will work as expected.

I know the differences between zero-length string and Null can be hard to grasp - it's sort of like asking "When is nothing not nothing?" - I've been there too.

HTH a bit!

John

[Modify_inc]

Hi -

When entering data in a form, entering only blanks in a field, or deleting the contents results in a Null for the field; there is no way of entering a zero-length string into a control, unless you use VBA.
In table design view, check the setting of Allow Zero Length for the Middle Initial; unless you changed it, it probably is set to NO, meaning the zero-length string - "" - is not allowed. It's important to note that the zero-length string is not Null.

Now, what's happening to generate all those errors is that the Trim function, when applied to an all-blank (or zero-length) field coming from Excel, results in in a zero-length string, which is not valid.

Here's what you can try:

Either change the Allow Zero Length property to Yes, so at least you can get all the data in without errors, or
Modify your (update?) query to replace blank strings with nulls: iif(len(trim([Initial])) = 0, Null, [Initial]) so that you will have no zero-length strings.

The second option might be better, as your null tests will work as expected.

I know the differences between zero-length string and Null can be hard to grasp - it's sort of like asking "When is nothing not nothing?" - I've been there too.

HTH a bit!

John

Thanks for the reply, you seem very knowledgeable of Access. Your explanation of the zero-length string vs Null was very helpful.

One way I can look at it, is a zero-length string is still a string, but null is nothing which answers your "When is nothing not nothing?" I would say when it is not null.

Hope I'm right, lol

Anyhow I will try your suggestion, as I'm sure it will work. It all makes sense.

Though I am having another issue with Access and it's about to get the best of me, at least for tonight. I will probably just post another topic since it's a completely different issue.

It's almost like it's a glitch of some sort. It works, but when I save and close the form, and reopen, my contact image is gone. If I drag any field or row while in Layout View and then hit the undo button, it re appears. It's so aggravating! I can't figure it out, I even rebooted the PC hoping it was just an isolated incident but it continues.

The one thing I did notice is working in Layout View, I can scroll way outside of my design by clicking with the mouse and holding, and dragging left or right. It finally stops at about 3 times the length of what my design is.

I'm still learning and haven't figured out how to create borders, or margins. I can drag the outside orange border, but it's like a window on a PC, I can still scroll inside it using the mouse as I mentioned above.

I think that might have something to do with it not displaying the attachment picture randomly. I tried deleting the empty cells, but since it's tables and not Excel, I'm not having any luck.

Update: Just noticed also on the left hand side where the contact picture is suppose to be displayed, that the time stamp date is cut off. It is only displaying the PM or AM, and not the whole date and time as it usually does for the comment section.

I am using the Contact Web Database theme provided by MS if that helps. I'm pretty sure I mentioned that earlier, but doesn't hurt to reiterate.

Thanks
Mike

[John_G]

Hi -

Glad I could help.

Please do post your latest issue as a separate thread - you are using A2010, and I am still on A2003 which doesn't have layout view, though the office will be moving to A2010 "soon" - whatever that means!

Cheers

John