Results 1 to 5 of 5

Thread: Append Field

  1. #1
    Join Date
    Oct 2013
    Posts
    3

    Unanswered: Append Field

    I have a db with a field that has been assigned as a number data type rather than a text. When a barcode with leading zeros is scanned into the field, the leading zeros are dropped. I've changed the data type to text, but how do I go about adding leading zeros to over 800 entries?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a query
    Code:
    update mytable
    set mycolumn = format(cint(mycolumn),"0000000000")
    where:-
    mytable is the name of the table
    mycolumn is the name of your new text column
    "0000000000" is the number of digits in the barcode you want, in this case 10

    whats going on is
    we convert the column back into integer, then using format pad with leading 0's, then assign the value of that format back to the same column
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    3
    When I run that query, I loose all the data in that field.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is that column?
    Ive tested the code here and it works fine
    if you want leading zero's then it becomes a string NOT a number.

    you could try a format on the column itself

    you could intercept the barcode reader input and strip off leading zero's

    I seem to remember that when I first started using barcode readers you could also configure the barcode reader to strip off leading zero's, although to be fair thats over 25 years ago
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    3
    This is an Access db and I am trying to use an iphone app to do inventory in classrooms. I've changed the Data Type of the field from a number to text. Unfortunately the app does not give me any option to drop leading zeros.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •