Eliminating Spaces in a Field 3

[BooBoosGalore]

There's probably a simple solution to this question.

I have a field in my database for account numbers. And since the format of these account numbers vary significantly, I was wondering if there's a way, perhaps, via a validation rule, to eliminate spaces and I'm really not sure how to do it.

Any suggestions?

TIA

BooBoosX-)

 

[Vic88]

Use the TRIM function to get rid of leading or trailing spaces. If the space is in the middle then you might have to write your own function to take care of this.

 

[THoey]

There have been several posts asking for a function to remove spaces from within a string. If you do a search, I thin MichaelRed had a really good solution. I think I even posted one. Search the MS Access General forum. You'll find all the code you need...

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[NewfieGolfer]

If the values are in a table or form you can use an INPUT MASK to show the numbers a certain way and remove spaces.

chris

 

[BooBoosGalore]

I'm sorry if I were misleading. I don't want to eliminate the spaces from a string. I want to disallow the entry of a space in the account number field. That's why I thought that a validation rule of some sort would work.

I thought of the imput mask suggestion, however, there's just too many formats to include.

Sorry for the confusion.

 

[THoey]

Since you don't want to format it, I would still suggest that you perform the space removal in the BeforeUpdate of the record. That way, the user can type however many spaces they want, but the data in the table will have the spaces removed. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dneufarth]

As stated above, the BeforeUpdate event could check for spaces using the InStr function. If present, set the focus back to the account number control and pop up a MsgBox to notify the user. They can go back and edit their entry.


Dave

 

[MichaelRed]

Or, use the basSpllit which should be available in the Ms. Access forum in the before update event (use just Split if you have the 2K version). You really DONT need to bother your users to just do formatting, although most would eventually get the message.

Private Sub txtAcctNum_BeforeUpdate(Cancel As Integer)

Dim MyInput as Variant
Dim Idx as Integer

MyInput = basSplit(txtAcctNum)
For Idx = 0 to UBound(MyInput)
txtAcctNum = txtAcctNum & MyInput(Idx)
Next Idx

End Sub



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

MichaelRed, I gave you credit in an earlier post in this thread... ;-) Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dneufarth]

BooBoosGalore,

Perhaps some examples of the various forms an account number may be entered will add some light to this discussion.

Do they vary by more than just number of characters?

Are imbedded separators other than spaces permissable?
Example: 123 456 7890 is not acceptable, but 123-456-7890 is.

Dave

 

[MichaelRed]

Terry M. Hoey,

Thanks for the 'credit'. I think/thought "BooBoosGalore" didn't understand and was attempting to illustrate the techique. I don't believe I got the message through.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[BooBoosGalore]

Dave,

Your example is correct. And, yes, the account numbers vary in length of characters and by format.

The account number field reflects an account number associated with any utility purchase with any supplier primarily in the states of PA, DE and NJ whether it be electric, gas, water, sewer, or telephone. So the scope of formats is quite broad.

One primary supplier uses two formats:

xxx-xxx-xxxxxx​

xx-xxx-xxxxxx​

Other formats for other suppliers include:

xxxx-xxx-xxx-xxx​

xx-xxx-xxx-xx​

xxxxx-xxxxx​

xx-xx-xx-xxxx-x-x​

xxxx-xxxx-xxxx​

The presence of the dashes makes the account numbers easily identifiable to the users as to the utility supplier. However, after countless meetings, memos and emails on proper input of these numbers, it has become clear that the field needs to be adjusted to not allow any spaces.

I was thinking that some kind of validity check to not allow spaces in that field would be the easiest thing to do considering the infinite number of formats.

In any case, I appreciate all the feedback from everybody. It's comforting to know that there's a forum where people freely share their time and expertise to users like me.

Thanks again,
Boo

 

[NewfieGolfer]

You can use a validation check. Set the validation rule to:

Not Like "* *"

and enter validation text, Maybe:

Incorrect Account Number Format

or something like that.


Hope this helps

Chris

 

[BooBoosGalore]

Yayyyyyyyyy!!!! Thank you, thank you, thank you, Chris! It works!!!! (Why didn't I think of that?)

Forever grateful to everybody's help.

Boo

 

[hsitz]

I'm using the method suggested by Michael Red using the Split function in Access 2000, but I had to make a couple of modifications.

First, I got an error with using the function in the BeforeUpdate event, so I moved it to the AfterUpdate event.

Second, an extra line was needed to blank out the original field before you rebuild it using the Split array.

Here's what I'm using:

Private Sub txtAcctNum_AfterUpdate ' event changed here

Dim MyInput as Variant
Dim Idx as Integer

MyInput = basSplit(txtAcctNum)

txtAcctNum = "" ' This is a new line

For Idx = 0 to UBound(MyInput)
txtAcctNum = txtAcctNum & MyInput(Idx)
Next Idx

End Sub

Thanks Michael, you saved me some precious time. -- Herb

 

[guestuser]

If you put the line

If KeyAscii = vbKeySpace Then KeyAscii = 0

in your 'On Key Press' event of the text box, the space key will not be allowed.

 

[dgillz]

Guest-

Can you display a message of "Spaces not allowed" or something along those lines?

Software Support for Sage Mas90, Macola, Crystal Reports and Goldmine