Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2004
    Posts
    28

    Post Unanswered: Text field TO NUMERIC FORMAT!!!!

    hi to who it regards(reader)
    I have table in which i have numbers they all show up in toward left corner when i export from acces tabel to excel which means text format now its not supose to hapenthat way , they need to be in numeric value?
    my table looks like this
    Table: |TD_EQ| |TD_EQ|
    |3_____| |_____3|
    |17____|--------->needs to be |____17|
    |21____| |____21|

    and i have to do this through query
    i have already try this IsNumeric([TD_EQ]) it doesnt work
    now its little history
    Its a text that was imported to acces 2000 and in delimited format no other format can work .all data type in Text. cant change data type other than that..so now what shoud i do to make it happen in NUMBERS so i can do calculation.
    Last edited by njptown20; 09-28-04 at 15:00.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you can't change the datatype, then obviously you have non-numeric values somewhere in the column. Maybe you should look at that instead.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Posts
    28

    Unhappy

    No it didtn help..its really hard to explain
    ok i am gona get this file from this person from excel later on and which i willl be recieving it in txt....so i made copy of his excle sheet and convert in into text manually now ..issue is wen i save it it wil be save in delimeted because..when i go about importing the file it wont show table correct form until at all so delimeted Export text format only works and give me right table.....now later one through query adn sorting i am supposeo to do calculation it wotn do it becuase its alll text ! so i am like stuck for two days in this problem ! so now u know why i am asking for help in changin format of text to numeric ..in records i have two different ways numbers are given
    1. 123
    2. "1,212"
    and they rare showed in various places !!!!

  4. #4
    Join Date
    Sep 2004
    Posts
    28

    Table

    <------------------------ Trades -----------------------------------> <---------------------- Transactions -------------------------->
    Trade Date Trade Date Trade Date Trade Date Date Date Trade Date Trade Date
    Equities Options Bonds Total Equities Options Bonds Total
    2/10/2003 PDB* 646 21 7 674 585 22 5 612
    2/10/2003 LDP 1,482 27 22 1,531 1,572 44 30 1,646
    2/10/2003 IOP 16,651 16 370 17,037 17,621 16 399 18,036

    *Note: DPB Transactions data is based on settlement date.
    IPB and GPB is based on Trade date.

  5. #5
    Join Date
    Sep 2004
    Posts
    28

    Smile

    thats an actuale table in text format

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh... some of the numbers are being stored in the table with " s around them? You need to fix that. do you make sure to tick " as a string qualifier when you're importing.. that will remove the quotes. Otherwise you need to run an update statement and remove those.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2004
    Posts
    28
    no i am a beginer in this field, i have not enough clue form what to do next much !....i am enterign this reality base first time..so does need extreme help in walking this path!

    ook i did what u told me todo and it did took care of " now they are gone
    but wen i still transfer text to excel it still hadd to click twice to edit and press enter to make in numbers..so now i need sum value to make my field numeric!
    Last edited by njptown20; 09-28-04 at 14:32.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    When your importing the data into access, go very slowly. There will be an option called "text qualifier". Be sure to select double quotes as the text qualifyer, this should solve your problem.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2004
    Posts
    28
    ook i did what u told me todo and it did took care of " now they are gone
    but wen i still export text to excel.. i still had to click cell twice to edit and press enter to make in numbers..so now i need sum value to make my field numeric! and that follows with every cell there with numbers. i tried
    IsNumeric([TD_EQ]) it gives me in equities row -1 , 0 , -1
    Last edited by njptown20; 09-28-04 at 14:42.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Change the field at the table level. Go into design view for the table, and simply change the field type for the column in question.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Sep 2004
    Posts
    28
    IT WORKED !!!!! thanky uo so much !!
    Do i have to worrry about 0 s at booton of every field i chagne into number ! ...

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No, that's just the default value for the next row should you choose to add one.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Sep 2004
    Posts
    28

    Exclamation

    ok in import wen i aded field as double ...it gave me import error!!!...

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You still need to import it as text. You can't strip the " out until after it's in the access db. You can't get quotes into an access db unless you import as text. It's going to have to be a two-step process until you can get the original datasource in a proper format.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Sep 2004
    Posts
    28
    ok i am littel confuse now ....
    this what i hav done
    taked txt import specifications
    *delimited
    *TAB / Text qualifier
    *10 field
    *no primary key
    * tbale = temp_mlprime

    Query:
    *<>" " in criteria od either first or second field (eliminates extra rows)
    thats what i done so far and it give e data what i need exactly to be shown!

Posting Permissions

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