Page 1 of 2 12 LastLast
Results 1 to 10 of 13

convert field data type from text to number

This is a discussion on convert field data type from text to number within the Microsoft Access forums, part of the Question Forums category; how does one convert a field from text to numbers in access? in my table, one field for customer numbers ...

  1. #1
    ajm
    ajm is offline
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA
    Posts
    1,473

    Default convert field data type from text to number

    how does one convert a field from text to numbers in access? in my table, one field for customer numbers is stored as text. it contains proper numbers and also "text" numbers where the number starts with an alpha character. I have tried to change the format of the field in the table set up. I have tried to multiply the customer numbers by 1 to create real numbers.

    but i keep losing data. I am currently trying to write an "iif" function to the effect that if the customer number starts with an alpha, leave it as is, else multiply by one.

    Can anyone please help with a solution?

    ajm
    Quote of the day from an Australian band:

    "If your dad hasn't got a beard, you've got two mums!"

    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,235

    Default Re: convert field data type from text to number

    You can use the VAL function in a Query to convert numeric entries entered as Text to Numbers, but I question this part of your question:
    it contains proper numbers and also "text" numbers where the number starts with an alpha character
    What do you mean it starts with an alpha character?
    If you have alpha characters, like "A23", what is the rule for converting it to numbers?
    Perhaps a few examples of what your entries look like would clear that up.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2011
    Posts
    146

    Default Re: convert field data type from text to number

    Try this:
    Cust#: iif([CustomerNumberTextField] like "#*",val([CustomerNumberTextField]),[CustomerNumberTextField])
    Hope that helps...
    Phil...

  4. #4
    ajm
    ajm is offline
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA
    Posts
    1,473

    Default Re: convert field data type from text to number

    Quote Originally Posted by Joe4 View Post
    You can use the VAL function in a Query to convert numeric entries entered as Text to Numbers, but I question this part of your question:
    What do you mean it starts with an alpha character?
    If you have alpha characters, like "A23", what is the rule for converting it to numbers?
    Perhaps a few examples of what your entries look like would clear that up.
    thanks joe for responding. i use the term number loosely. our customer "numbers" (lets call them customer ids, for clarity's sake) used to be all numeric characters but we now have started using customer ids that start with a single alpha character followed by 5 numerals. eg A62144. so now the customer id field contains both types of customer numbers.

    I might add that the error is actually at the import stage so am now trying to figure out why access 2007 won't import these new alpha numeric customer ids but will let me paste them in. that is, i can't link a table as the alpha numeric customer ids result in a Num! error, but i can copy the entire record set and paste it in as a table with customer ids appearing perfectly.

    weird.

    cheers, ajm
    Quote of the day from an Australian band:

    "If your dad hasn't got a beard, you've got two mums!"

    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  5. #5
    Board Regular
    Join Date
    Feb 2011
    Posts
    146

    Default Re: convert field data type from text to number

    Are you using an Import Specification during the import? If so, perhaps it's looking for a number rather than text in that field.

    Phil...

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,047

    Default Re: convert field data type from text to number

    ajm

    How exactly are you importing the data?

    You should have no problem importing numeric data into text fields.

    If you need to do something later with numbers that are stored as text, eg calculations, then you might need to convert them to 'real' numbers.
    If posting code please use code tags.

  7. #7
    ajm
    ajm is offline
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA
    Posts
    1,473

    Default Re: convert field data type from text to number

    phil, norie,

    i have the list in an excel table and i initially tried to link the table using the Get External Data - Excel Spreadsheet wizard and then tried importing the table using the same. both attempts resulted in the alpha numeric ids coming up as #Num!. intersetingly, when previewing the data prior to linking, the alpha numeric ids are all there.
    Quote of the day from an Australian band:

    "If your dad hasn't got a beard, you've got two mums!"

    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  8. #8
    Board Regular
    Join Date
    Feb 2011
    Posts
    146

    Default Re: convert field data type from text to number

    In the attempts where the alpha numeric ids came up as #Num! did you look at the design of the table? While excel is more flexible with how data is formatted in its columns, Access is not. To attempt to experience what you're going through, I linked an excel spreadsheet to a database. I too received those #Num! errors but only in the instances where Excel had a different data type than what the table design allowed.

    Try selecting the column that contains your alpha numeric account ids and format those cells as text before linking or importing into Access and see if that improves your results.

    Phil...

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,047

    Default Re: convert field data type from text to number

    When importing Access will take a guess on the data type of each field.

    It bases this on the first few rows of data.

    So if that results in an incorrect guess you can end up with this sort of problem.

    If that is the problem there are at least 2 ways to fix it:

    1 Change the data in Excel so it is definitely all the correct data type - eg if there a are numbers make sure they are stored as text

    2 Import into an existing (blank?) table where the data types for each field are already set.

    I'd recommend 2 because 1 might have implications on the source data.

    It's also easy to implement 2, just create a new table with all the required fields.

    Alsoe, if you do import to this 'intermediate' table you can append the data to another table in Access.

    Then you can clear out the 'intermdiate' table ready for the next import.
    If posting code please use code tags.

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,235

    Default Re: convert field data type from text to number

    To follow up Norie's previous response, another option, somewhat similar to option 2 is to export the Excel file to a CSV file (or some other text file). Then you can import the text file and tell Access exactly what the format of each field is (instead of leaving it up to Access to "guess", which is what it does when you import files directly from Excel).

    Imports directly from Excel are often problematic because of this "guessing..."
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com