Input Mask To Add Numbers

[PU36]

How do I add 0's (zeros) to the first part of a text field.

For example, currently when a user created a record it goes, 1a, 2a, 3b.... but then they get to 10a, it is viewed as 1a, 10a, 11a, 2a, 3b,....

I want to be able allow the user to input 12a, but have access put 000012a as its record. With this input, the ascending order will look normal.

Any ideas?

 

[PHV]

You may try @@@@@@@, ie leading spaces.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PU36]

Thank for the reply, but that didn't resolve it. It only put @ in the places where I want a 0.

 

[PHV]

So, no input mask at all.
In the AfterUpdate event procedure of the textbox control:
Me![name of textbox] = Right("000000" & Me![name of textbox], 7)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PU36]

The name of the textbox is SubID.

I input the following into the After Update within the properties of the SubId text box. The form is generated through a query of the table. It looked like this..

Me![SubId] = Right("000000" & Me![SubID], 7)

It took the express, but it did not change the value.

Should I put it somewhere else, or did I put it in the wrong location?

 

[PU36]

I even tried just the following;

=Right("000000" & [SubID],7)

on the "On Change" and "On Exit" without any luck.

 

[PHV]

In the AfterUpdate event procedure of the textbox control

Click the ellipsis (...) and write the VBA code in the function body.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PU36]

That worked. Thank You

Now I just have to figure out how to tell the ascending order that 1, 2, 3, 3a, 4 is the order and not 1, 2, 3, 4, 3a.

 

[PHV]

ORDER BY Val(SubID),SubID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PU36]

Where do I put that value?

 

[PHV]

This is SQL code for sorting a query.
For your form, have a look at the OrderBy and OrderByOn properties.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PU36]

I beleive that Access looks at 3a is a two digit value where 3, 4 is a single digit value and will always come first. Then when 10 comes around which is also a two digit value, 10 comes before 3a.

I think the ascending is working, but it isn't what I would hope for.

I'm trying to tell Access that 3a comes before 4...