Text Box formating question

[mrrrl]

I am using MS Access to access a SQL7 database. I am using a unbound text box that the user can enter a number in. The field in the SQL database is a [varchar] 5 NULL. How do I format the users input from the text box to match the field in the database so when I use it to query I get the correct record back. I need the users input to be “ 2” ,right justified with leading spaces, 5 characters total and not “2”.

I have tried input masks but they enter data left to right.

Thanks

 

[HiBoo]

I don't believe you'll get the results you're looking for without changing the data type to char(5). I was looking to do something similar and didn't resolve my problem. I developed a function to concatenate spaces to the left of the users entries but it would not store them. I ended up scraping the function b/c it was useless.

Please post a solution if you happen to come across one though.

 

[StewartGW]

Set the Text Align property of the text box to Right. The SQL command statement does not care if the value is ' 2' or '2'.

 

[mrrrl]

No, that did not work. It only works if I put in 4 spaces in front of the 2. I don't want to have the person using the the program to have to put spaces in front of the number.

 

[HiBoo]

Is there any particular reason you need to store leading spaces? Like StewartGW says, it matters not to SQL if there are spaces there. If the data you are trying to store is always going to be numeric then it might be a good suggestion to lead the users input with zeros rather than spaces. This might be the visual effect you're looking for but again, you might want to question the need for leading spaces or zeros for that matter. Displaying the data and storing the data are two different matters. Best to store as little data as possible to conserve space and display the data formatted how you like.

 

[mrrrl]

The database is a 3rd party medical database and this is the way it is setup, so I have to use the records and field as they are.

 

[jfischer]

You can use the AfterUpdate event of your unbound text box to format the data the way you need it. For example:

Private Sub Text1_AfterUpdate()
  Dim i As Integer
  
  i = Len(Me!Text1)
  If i < 5 Then
    Me!Text1 = Space(5 - i) + Me!Text1
  End If
End Sub

 

[dneufarth]

You might also try this in the query rather than just the text box name.

Right(Space(5) & me.textboxname,5)


Dave