Simple input mask

[BPB6]

I am trying to create a simple input mas for a number for my table. I am using a cvs database from a different source that gives me a simple invoice number. I want to change the invoice from 4 to 1*4*0 so that a barcode scanner can read it an find it. When I enter the input mask of "1*"0"*0". The only way I can accept more than a sigle digit invoice is to add additional numbers ie "1*"00000"*0". The problem is that this will leave a space in the string instead of the invoice followed by the *0 when I am only entereing less than the numbers in the mask. The barcode scanner cannot read that space. How do I get the input mask to not add the additional space on a single or double etc. invoice number. I have searched this forum and other and cannot seem to find the solution.
Thank you for any help.

[rpeare]

I haven't worked with bar codes really but wouldn't you want your field to look like:

1*0*0*0*0*0*0*4

where all the leading 0's will give your order numbers a chance to increase and not exceed the limit of your bar code or would your code be something like:

1*4*0 for order number 4 and

1*45*0 for order number 45 etc

can your barcode grow if your order number becomes more than single, double, or triple digits

If it's the latter you do not need to use an input mask you can create your barcode label on the fly with something like:

Barcode: "1*" & [ordernumber] & "*0"

where if your order number is 101 it will appear as

1*101*0

and if you want the leading 0's to be shown you would have to know the upper bound of the length of your string, so let's say you wanted 1*000000*0
where the 000000 could be any six digit number you would do something like:

Barcode = "1*" & right("000000" & [ordernumber],6) & "*0"

where if your order number was 101 it would appear as

1*000101*1

It's not clear what you want a bar code with multiple digits to appear as in your question.

[BPB6]

Rpeare,
Sorry about not being more specific. Since the source data on the invoice number is an integer from 1 to infinity it comes in to the database as such. I need to have it changed to 1*[invoice number]*0, regardless of how big the number. I cannot change the source data as it is from another program I purchased. The barcode scanner will read the invoice as 1*[invoice number]*0 without any spaces or extra zeros between the astericks. ie. So as I import the data into the invoice the invoice will import as 5 and the table would change it to 1*5*0 and if the invoice was 555 it would change it to 1*555*0. Hope that helps with finding a solution.
Thanks!

[rpeare]

Barcode: "1*" & [ordernumber] & "*0"

If you set this up as a field in a query it will show you want you want to see

I wouldn't use an input mask, you don't need it. As long as your order number is available you can create the string you want rather than having to create a specialized input mask that is going to make it harder to access your data.

[BPB6]

I have done this and you are definitely correct, however, I was hoping to keep it as a table. I am taking the database and uploading it to Access Ipad app, so that we can inventory products, pull invoices and "pick" orders in a warehouse environment. The "limitation" of the ipad app is that it only allows you to edit tables and not queries. I greatly appreciate your time answering this question, but is there a way to keep it as a table. I do not want to Query it as a table because as new orders are appended to the table I would like to keep it automatically in-sync with out the extra step of query building each time. If not I will have to approach it from a new angle.

Thanks,
BPB6

[rpeare]

Ok I'm stumped, I don't do much with input masks because I rarely get them to do what I want. What it amounts to is that the 'spaces' in your order number are not actually spaces, they are null placeholders which I can't for the life of me figure out how to condense so you don't see them.

There are a few things you could do though.

The most brute force way is to create a new field in your table that's something like BarCodeConv and convert the barcode to something your reader can interpret.

If the order number is a string (and I think it must be if you're applying an input mask to it) you can set up a conversion at the time of data entry so for instance in your ON ENTER property of the ORDERNUMBER field you could have

If Not IsNull(OrderNumber) Then
    OrderNumber = Right(Left(OrderNumber, Len(OrderNumber) - 2), Len(Left(OrderNumber, Len(OrderNumber) - 2)) - 2)
End If

and in the ON EXIT property of the ORDERNUMBER field you could have

OrderNumber = "1*" & OrderNumber & "*0"

[BPB6]

Thanks. I am a novice and for the life of me I cannot and do not know where the On ENTER property is. I know where to find it in forms and reports, but not in the field of a table. Sorry, and thanks.

[BPB6]

Ok I'm stumped, I don't do much with input masks because I rarely get them to do what I want. What it amounts to is that the 'spaces' in your order number are not actually spaces, they are null placeholders which I can't for the life of me figure out how to condense so you don't see them.

There are a few things you could do though.

The most brute force way is to create a new field in your table that's something like BarCodeConv and convert the barcode to something your reader can interpret.

If the order number is a string (and I think it must be if you're applying an input mask to it) you can set up a conversion at the time of data entry so for instance in your ON ENTER property of the ORDERNUMBER field you could have

If Not IsNull(OrderNumber) Then
    OrderNumber = Right(Left(OrderNumber, Len(OrderNumber) - 2), Len(Left(OrderNumber, Len(OrderNumber) - 2)) - 2)
End If

and in the ON EXIT property of the ORDERNUMBER field you could have

OrderNumber = "1*" & OrderNumber & "*0"

Rpeare,
Again thank you for the help. I believe I finally found the answer. In the format property of the ordernumber field I entered "1*"&&&&&&&"*0". For as many "&" characters I enter I can enter the equivalent integers for my input. There are no spaces left between the astericks and the ordernumber value.
Thanks again,
BPB6

[rpeare]

Odd I tried that and it still left placeholders. Well at least you got what you wanted.