View original (French)

[Access] Conversion de champ texte en nombre

slyleglobetrotter Posted messages 7 Status Member -  
YBouBou Posted messages 2 Status Member -
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 Posted messages 7 Status Member 14
     
    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 Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
       
      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
  4. 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
  6. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
     
    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! Posted messages 2 Status Member
       
      Hello,

      I'm struggling too much with ACCESS. Can you help me please?
      0
  7. 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 Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
       
      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
      1. elendis > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
         
        you 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
      2. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > elendis
         
        Hello,

        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
      3. zenon Posted messages 729 Status Member 180 > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
         
        Hello 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
      4. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > zenon Posted messages 729 Status Member
         
        Too 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
  8. nadjlaa Posted messages 38 Status Member 1
     
    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
  9. 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 Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
       
      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 > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
       
      The 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 Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
       
      So, 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 > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
       
      In 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 Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
       
      Are 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