Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Text to Number (2003)

    I know how to convert text to numbers using Val(), but how can I actually replace the text fields in a table with the numbers? Can it be done?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text to Number (2003)

    If a text field contains valid numbers, you can simply change the data type of the field from Text to Number and specify the correct field length. You'll get a warning if there are values that can't be converted.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Text to Number (2003)

    That's it. Some of the fields have a decimal point, and I do get a lot of errors when I convert. The text fields with a decimal are rounded.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Number (2003)

    As Hans points out "specify the correct field length". You'll need to change the Field Size from Long Integer to something other than an integer, like Double.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks guys. Sorted it out. Did a report as Hans suggested.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can this task be done using code?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='vanleblanc' post='772612' date='28-Apr-2009 03:15']Can this task be done using code?[/quote]
    Yes, by executing the appropriate SQL string:

    Dim strSQL As String
    strSQL = "ALTER TABLE [TableName] ALTER COLUMN [FieldName] INTEGER"
    CurrentDb.Execute strSQL, dbFailOnError

    This will change the field FieldName in the table TableName to a Number field of size Long Integer.
    If there are text values that cannot be converted to a number, an error will occur.

Posting Permissions

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