Mask out leading space and trailing space from a string of characters

[mainerain]

After a long time figuring out that my problem was due to a space before a string of characters I realized I needed to prevent a space before and after a string of characters, but not inside the string.



Thanks, will check tomorrow, past my bedtime

[davegri]

Mystring = Trim(Mystring)

[mainerain]

Right answer to the question. However wrong question. That's the real battle, asking the right question.

A better question might be: How to build an input mask that would not allow a leading or trailing space and allow for variable length string with an underscore in the midst. I tried, I'm not sure it's possible.

[Micron]

Doesn't sound possible to design it to me either. While you could build one, the impossible part might be deciding how many optional characters to allow for, plus how would the user know that the n, x and y positions are not optional? You can build the input mask in code, so if you can identify the condition you could build the string in a Select Case block? The InputMask property belongs to the textbox control. I've never done this though.

[orange]

100% true: the real battle, asking the right question.


Perhaps to clarify exactly what is required, you could post some typical values. Probably better to show "acceptable and not acceptable values" and where exactly this will be used. If any of this is the responsibility of a user, then explicit instructions/training may be required. If it is"free form by user", then all bets are off. Why input mask? You could parse user input against validation info and adjust anything a user enters to meet your requirement.

[isladogs]

I wouldn't even attempt to do this with an input mask. They often make life more difficult for end users.

Just run code to trim the textbox input string in the after update event.
Also, only the leading space(s) are an issue. Access will ignore any trailing spaces anyway.

[Micron]

I would trim in BeforeUpdate. Wouldn't use a mask as I noted but how does that simple approach accommodate an underscore character in a seemingly random spot?

[isladogs]

I would trim in BeforeUpdate.

Hmm.... Not sure how that would be possible.

BeforeUpdate is used for validating data ... not for modifying it.
Attempting to trim the entered data using code in before update event would lead to a runtime error.
Am I missing something here?

[Micron]

Probably not.
Not sure I will be able to prove you right until tomorrow. However it is usual to not permit incorrect values with the intention to modify to make the values conform?

[isladogs]

Not sure I will be able to prove you right until tomorrow

Love that comment ....
Here's an example



It works fine in the AfterUpdate event

[orange]

That's pretty clear.
I'd still like to hear the rationale for this requirement from the OP.

[Micron]

Colin, I have to agree with you and I forgot about that runtime error. I presume that a form update event will take care of editing the record (the Trim'd value) as long as the control is bound?

[isladogs]

Using exactly the same code in the control's after update event trims the entered data and updates the record accordingly.

[mainerain]

This is what I ended up with, for now anyways:

  Private Sub tbxWOInfo2_Click()
  Dim S1 As String, S2 As String
   
  S2 = tbxWOInfo2.Value
   
          S1 = Trim(InputBox("ENTER DESCRIPTION AND WO#" & vbNewLine & vbNewLine & "Example: C-7 PM_T103892", "Statement")) ' TRIM ANY LEADING SPACES ON INPUT
          tbxWOInfo2.Value = S1
          
          If S1 = "" Then
          tbxWOInfo2.Value = S2
          End If
  End Sub

This deals with the leading edge, avoids the pitfalls of making an input mask, and deals with keeping previous input if user decides to cancel.

Thanks for all reply's