Removing spaces from long number

[leehi82]

Hi Guys,

Please could someone help me?

I've created a database for work that stores users information, each user has a unique identifer (UUID) which is a 12 digit number however the place we get our information from displays the 12 digit number like so 1234 5678 9123 as you can see this string contains spaces and my staff like to copy and paste this in to the database but i don't want the spaces included so the forms i have created will only accept 12 digit numbers without the spaces.

Is there a way i can let my staff copy and paste the string with the spaces and access will automatically remove the spaces?

Any help would be greatly appreciated!

Cheers,



Lee

[Bob Fitz]

You need to use the Replace() function. Something like:
Replace("1234 5678 9123"," ","")

[Welshgasman]

How do you intend to paste? I ask as if the control only accepts 12 chars, then you need to replace before pasting, in some way.

[leehi82]

I could change the contol to accept 14 digits and then do the replace, is there some vba code i could use to automate it?

[Welshgasman]

Use the replace in the afterupdate event of the control and check you have 12 chars after.?

[leehi82]

Thank you for your replies, and forgive me for being a little dim but could someone help me out with what code i need to type in VBA please?

[Micron]

I might use control BeforeUpdate with the expression that Bob suggested. I wouldn't limit the input to x characters as that may change in the future - just remove the spaces.
EDIT - well that seems to cause some sort of conflict in my testing
(the macro or function set to the before update or validation rule property...) so if this is a new record perhaps form BeforeInsert or control AfterUpdate as suggested.

[Welshgasman]

Something along the lines of

Me.UUID = Replace(Me.UUID," ", "")
If Len(Me.UUID) <> 12 Then
    set error message here
    Me.UUID.SetFocus
End If

Or perhaps try the Form or control BeforeUpdate event and add Cancel = True after the SetFocus.?
I believe you would use the .Text property then, and not the .Value property?

https://docs.microsoft.com/en-us/off...reupdate-event

[Micron]

One possible downside to using form update events is that you won't see the effect until you leave the record. If that's on a datasheet or continuous form it might be acceptable to the user. If you navigate to another record on a single record form, you won't see the result until you go back to that record.

[CJ_London]

If this is just for the benefit of user input - have you considered using the input mask?

9999\ 9999\ 9999;;” “

Users can copy with spaces and value saved as a number (would need to be a double type of that size) or text if you prefer

[moke123]

Function NoSpaces(pstr As String) As String
Dim strHold As String
    strHold = RTrim(pstr)
    Do While InStr(strHold, " ") > 0
        strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
    Loop
    NoSpaces = Trim(strHold)


End Function


Private Sub UUID_LostFocus()
    Me.UUID = NoSpaces(Me.UUID)
End Sub