View original (French)

[Access] Conversion de champ texte en nombre

slyleglobetrotter -  
Hello forum,

In an Access data table, I have a field where the data has been set to "text" type. The problem is that these are actually numeric data...

Is there a way to quickly convert the type of my field without losing information or modifying my database?

Thank you for your help.
Sylvain
Configuration: Windows XP Firefox 2.0.0.1

8 answers

  1. slyleglobetrotter Author
    Hello,

    Thank you for your help.

    Indeed, this is what I tried to do: switch to design mode and change the data type. But Access tells me there are errors and that the data will be lost... That's where it bothers me ;-)

    I am looking for a solution so as not to lose this data.

    Thank you,
    Sylvain
    12
    1. blux Moderator
      There is somewhere in the table, data that is not numeric!

      You can find them to modify, I suggest a query that displays the field, its numeric status, its ability to be converted to a number, and whether it contains spaces (since space is part of a number):
      SELECT Table4.toto, isnumeric(Table4.toto), cdbl(Table4.toto), instr(Table4.toto," ") FROM Table4;
      This can give results like this:
      toto Expr1001 Expr1002 Expr1003 12 -1 12 0 a 0 #Error 0 17 -1 17 0 1 2 -1 12 2 12 -1 12 1 35 -1 35 0
      Then, you just have to modify the non-numeric fields...

      --
      See you Blux 
       "The fools will try anything. That's how you recognize them"
      0
  3. mini.heli
    Hello everyone,

    My name is Guillaume and I am an adoption Data Manager...

    I have been using Access for a short while.

    I only work in design mode.

    The problem:

    At my workplace:

    In a table, when I want to convert a data type from "Text" to "Numeric" (Double real, scientific, auto):

    In my fields, 4.6e-005 becomes 4.60E-05; 36 becomes 3.60E+01.

    Perfect!

    When I get home:

    When I try to reproduce the action, I get the following error message:

    Microsoft Office Access has encountered errors while converting data.
    The contents of the fields of 355 records have been cleared.
    Do you still want to proceed?

    Can you help me?

    Thank you.
    2
    1. snay
      One solution could be to add 1-1 to your text field.

      valueNum : [valueTxt + 1 - 1]

      I hope it's not too late,

      Hi
      1
      1. zas
        I think rather that it's [valeurtxt ] + 1 - 1
        0
      2. snay
        my bad, that's correct.
        0
    3. blux Moderator
      Hello,

      you open your table in "design" mode and change the column type...

      If there's a problem, ACCESS will tell you (presence of letters or others).

      If, however, you don't want to change anything, then you'll need to convert the data "on-the-fly," when using it (if you're performing calculations on it...)

      --
      See you later Blux 
       "You can count on idiots to do anything. It's even how you recognize them."
      0
      1. helpplease!
        Hello,

        I'm struggling too much with ACCESS. Can you help me please?
        0
    4. elendis
      Hello,
      I have a similar problem:
      I am working on a database where the first 10 columns are text and the following 150 are numeric. The problem is that all the numbers in these latter columns that don't have any digits after the decimal point are recognized as text and have a ' at the beginning.
      If I select all this data and go to Format>Number, it doesn’t change anything due to the little '.
      What I really need to do is select a column>Data>Convert. However, I would have to repeat this operation about 16,000 times. In other words, it’s a nightmare.
      Since this is a function I need to repeat, I thought perhaps a macro could do it for me. But I’m not good at VBA, and I’ve been struggling for a while, which is why I’m reaching out for help!

      So here it is: Do you know of a little macro that would allow me to automate this manipulation?

      Thank you a thousand times in advance!!!!!! :-)
      Elendis.
      0
      1. blux Moderator
        But I'm bad at VBA
        And I'm bad at Excel...

        Thank you for creating a new message...

        --
        See you Blux 
         "Fools dare to do anything. That's how you recognize them"
        0
      2. elendis
        @bluxyou may not have to be so unpleasant................
        No other messages, thank you.
        I found the answer to my problem on another forum that is much more welcoming.
        0
      3. blux Moderator
        @elendisHello,

        I didn't mean to be unpleasant. I simply pointed out that the topic was about ACCESS, while you asked a question about EXCEL (without mentioning it, by the way; it's left to us to play guessing games!). It is clearly stated on this forum that we should avoid mixing genres so that everyone can find an answer to a previously addressed issue without having to go through all the messages.

        The initial message is about ACCESS, and I suggested that you create a new message about EXCEL.

        As for finding an answer elsewhere, in a more "welcoming" forum (sic), feel free to go there...

        --
        Cheers, Blux 
         "Fools dare everything. That's how you recognize them."
        0
      4. zenon
        @bluxHello Blux,

        In my opinion, the reaction once again comes from your signature...

        Regulars know what it's about, but for newcomers (I was one), it can create a misunderstanding.
        0
      5. blux Moderator
        @zenonToo bad!
        They should read the charter and see who posts, before coming and saying I want...

        It's still strange that with 99.9% of the memories, everything goes well...

        --
        See you, Blux 
         "Idiots will dare anything. That's how you recognize them."
        0
    5. nadjlaa
      Hello everyone

      here's my problem, I have a table where the date field is numeric because I have presumed dates and I want to create a query that gives me the list of everyone who was born, for example, in 1980 (i.e., by year). So I'm struggling to solve the problem, i.e., how to do it.
      I have 20 out of 1000 presumed employees.

      it's extremely urgent
      thank you in advance

      nadjla
      0
      1. bottle
        Hello,

        I would like to know how I could, when entering text data in Access, immediately convert it into a numeric value in the table.
        For example, if I enter F, Access displays 1, or if I enter H, Access displays 2.

        Thank you and long live Audiard!
        0
        1. blux Moderator
          If your input is made through a form based on the table, we can do something; however, if it's entered directly into the table, there's nothing we can do...

          --

          A+ Blux 
           "The idiots, they dare everything. It's even how you recognize them"
          0
        2. bottle
          @bluxThe input will be done through a form based on the table. Phew!

          I also like (while we're at it) that a sitting intellectual goes less far than an idiot who walks

          Thank you
          0
        3. blux Moderator
          @bluxSo, I no longer see the point... Why not record H or F directly in the table?

          --

          A+ Blux 
           "Assholes dare to do anything. That's even how we recognize them"
          0
        4. bottle
          @bluxIn fact, it involves the fast entry of thousands of questionnaires.
          The raw data is in text and needs to be transcribed into numbers.
          This is the case for many questions (with a risk of errors, therefore).
          The conversion by Access would involve less risk.

          There you go!
          0
        5. blux Moderator
          @bluxAre the data already available in electronic form or are they just 'paper' data?

          --

          A+ Blux 
           "Stupid people dare to do anything. That's how you recognize them"
          0