How to save the input data format together with the value

I use Text control and ADO Control (Add new, update) to input data. I want to have the data to save in the MS Access by format such as X-XXX-XX. But I don't want users to input the "-". How can I write the data into database by using the format I define in the Text Control? But not to write a program, is there any property or parameter to do that?
Who is Participating?
DRRYAN3Connect With a Mentor Commented:
Where are you applying the format?  Which of the text control's properties or events are you using to reformat the entered data?
Hmmm... I've got no idea on how to really do that as I do not really know the format.

You may want to try using the Instr() function or the Left() function.

An example of the Left() and Instr() function:

MsgBox Left("This is the day", 4)

- Returns 4

MsgBox InStr(1, "This is the day", "the")

- Returns 9

If they're all numbers, you could also try using the Format() function.

Another function you might need is the Mid function which returns a specific number of characters:

Dim FirstLetter As Integer, LastLetter As Integer
FirstLetter = 1
LastLetter = 4

MsgBox Mid("This is the day", 1, 4)

- Returns "This"

That's it!

glass cookie : )
Some way to do it is to scan the string in the textbox, split them up and then add he  "-" together.  Something like this:

MyInput = Segment1 + "-" + Segment2 + "-"...

On the other hand, there might be a better way, so... good luck on finding a better answer!

That's it!

glass cookie : )
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


You are probably going to have to write a little code.  In the LostFocus event for the text box in question, use something similar to the following:

Private Sub Text1_lostfocus()
  Text1.Text = Format(Text1.Text, "#-###-###-#")
End Sub

substituting your format for the one I show.  When the user leaves the field, the field's contents are replaced with the new contents which will include the dashes.
rydvAuthor Commented:
Thanks for reply. DRRYAN3.

This TEXT control has been bounded to datasource and datafields. I checked and found that the text format is correct during debug, but I don't understand why "-" missing in the database...
rydvAuthor Commented:
use format function to assign to the field before running the UPDATE.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.