Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Angry Answered: Leading zeros in table! PLEASE HELP

    Hello everyone,

    I have a problem with a field in one of tables. There are leading zeros and i need to get rid of them in order to successfully join this table. However, i have tried so many query expressions (CLng, Clnt, Val,) and they delete all of the zeros, leading and trailing.

    I only want to delete the leading ones. Please help this is becoming a nightmare.

    I made this function in excel hoping that i could adapt it to Access by using mid but it doesn't seem to work either:

    =IF(LEFT(B2;3)="000";MID(B2;4;4);IF(LEFT(B2;2)="00 ";MID(B2;3;4);IF(LEFT(B2;1)="0";MID(B2;2;4))))

    Access:
    Poste k: VraiFaux(Gauche([En_Cours_Cdes_Forcaste]![Poste];3)="000";ExtracChaîne([En_Cours_Cdes_Forcaste]![Poste];4;4);VraiFaux(Gauche([En_Cours_Cdes_Forcaste]![Poste];2)="00";ExtracChaîne([En_Cours_Cdes_Forcaste]![Poste];3;4);VraiFaux(Gauche([En_Cours_Cdes_Forcaste]![Poste];1)="0";ExtracChaîne([En_Cours_Cdes_Forcaste]![Poste];2;2))))

    Its in french but its the same thing and it gives me the same results i.e taking out the trailing zeros.
    Last edited by Guy Winfield; 05-28-15 at 05:45. Reason: Tried Access formula

  2. Best Answer
    Posted by healdem

    "When making changes to a column in the database it is always a smart call to create a new column of the correct tyoe, then yse an update query to transfer the value to the new one. Ghen once you are happy the new values are correct decide what to do with the old column.

    As to why you were getting numbers in the range of 1..14 as opposed to the expected 1..999 I havent a clue. There isnt enough information here to help diagnose the pproblem. There is no table design, no data, no identifixation of what you tried and what the outcome was."


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have leading zeros in your table it infers that you are using the wrong datatype to stor ehte data.
    store numbers in a suitable numeric datatype (suitable as in integer or real/float, and size so that it can accomodate the largest expected number)
    store dates an or time in a datetime datatype
    ...never in a string/text datatype.

    so what I'd suggest you do is
    1) fix the source of the data
    2) fix the column containing the data (create a new column of the correct datatype (Im guessing its going to be an integer number and therefore use Integer or Long))
    3) then run a query
    Code:
    UPDATE MyTable set  MyNewColumn = cint(MyOLdColumn)
    CIN will coerce a string represtnation into an integer, if the underlying number should be a long then use clng

    ...fix the data, then fix the application and don't allow crap data into the system. doing a workaround will hurt badly over time.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    why not uses the REPLACE function and the Val()

    msaccess
    bla = val(replace([blabla],"0",""))


    now in execl the replace is differance so

    create a custom function

    Function MyReplace(Str, find, wiht)
    MyReplace = Replace(Str, find, wiht)
    End Function

    now in a cell you can call it and it

    =MyReplace(F6,"0","")
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by myle View Post
    why not uses the REPLACE function and the Val()

    msaccess
    bla = val(replace([blabla],"0",""))
    This, like the OP's attempts, will replace all of the zeros, not just the leading ones.

    Quote Originally Posted by healdem View Post

    so what I'd suggest you do is
    1) fix the source of the data
    2) fix the column containing the data (create a new column of the correct datatype (Im guessing its going to be an integer number and therefore use Integer or Long))
    3) then run a query
    Code:
    UPDATE MyTable set  MyNewColumn = cint(MyOLdColumn)
    Maybe I've missed it, but I don't see that you've said anything about where this data originally comes from, i.e.

    1. Being entered via the keyboard
    2. Imported from another application
    3. Copied and Pasted from elsewhere


    # 2, above, could possibly require a different approach, but if # 1, above, is the source of the data, and assuming that the current data populating the Field is all digits, all you need to do is to go into Table Design View and change the Field's Datatype, as the Jaded Developer suggested, from Text to Number/Integer or Number/Long Integer. You'll get a warning that 'some data may be lost,' but in this case (assuming, as I said above, that the current Field data is all digits) all that will be lost are the leading zeros, and that's exactly what you want!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #5
    Join Date
    Mar 2015
    Posts
    31
    Hello,

    First of all thank you for your quick responses !

    Now down to business, the source is an imported table from another Access Database and i can't fix it as it is not in my hands, and come the day it needs to be changed will demand a huge reshuffle.

    I already tried the replace and Val stuff and that took away all zeros as said previously

    Then I tried changing the field datatype and it unfortunately deleted some of my records so that didn't really work. I find it weird though as when i change the data type instead of having numbers varying from 1 to 999 i only get numbers from 1 to 14. Thus the lost records, but why would it do this ?

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes you can fix this. If you cannot make changes to the source if the problem, then make changes on how the data is used in your system. Just because someone else makes a lousy mess of things diesn't nean you should.

    You could run a series of queries which remove leading zero's. Eg
    Code:
    update mytable set mycolumn = mid (mycolumn,2) where left (mycolumn,1) = '0'
    run that query as many times as required till no moee leading zeros are found.

    However that will be a performance pig over time. Ideally you only want it to work on rows that havent yet been cleaned up. So if you have someting else that can help idenitfy rows to be cleaned, such as a date or ascending id, use it also in the where clause.

    What I tend to do when importing data from another system, where i dont trust the data is use a buffer table (s). Ill accept data into temporary tables, then import data i can process into the system deleting data in the holding tank that was accepted, leaving data i cant in the holding tank. get the users correct that data, then re run the import as iften as required.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    When making changes to a column in the database it is always a smart call to create a new column of the correct tyoe, then yse an update query to transfer the value to the new one. Ghen once you are happy the new values are correct decide what to do with the old column.

    As to why you were getting numbers in the range of 1..14 as opposed to the expected 1..999 I havent a clue. There isnt enough information here to help diagnose the pproblem. There is no table design, no data, no identifixation of what you tried and what the outcome was.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Guy Winfield View Post

    ...I tried changing the field datatype and it unfortunately deleted some of my records

    ...why would it do this...
    Notice, when I suggested simply changing the Datatype of the Field, I qualified it by saying, twice, actually

    Quote Originally Posted by Missinglinq View Post

    ...assuming that the current data populating the Field is all digits

    ...but in this case (assuming, as I said above, that the current Field data is all digits)...
    and I also said to use this approach if the data was being entered via the keyboard. My guess is that the 'missing' Records contained characters other than digits; that's the only reason those Records would have been dropped in the conversion. These characters may be alphas, which you can easily check by a visual inspection, or they may be special characters that were contained in the data when it was imported. This possibility was why I said a different approach (such as healdem is suggesting) might be needed if the data was imported.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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