Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Table Field Format (Access 2002)

    Hi

    Couldn't find the answer in the forum.

    I have data in one table which I wish to massage and put into a new table. The problem is a couple of the fields are text fields, but in the new table I wish to have them as number fields. Furthermore, for one of these text fields, the values are 8.5, 6.3 etc, which have to be retained in the new table as a number field.

    So my question is can I modify my SQL to change the formatting of a text field to a number field for the new table. If so, I would appreciate advice on how to accomplish this.

    Many thanks
    WTH

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

    Re: Make Table Field Format (Access 2002)

    The easiest way to do this is:
    1. <LI>Select the existing table in the database window.
      <LI>Copy it to the clipboard (Ctrl+C)
      <LI>Paste it (Ctrl+V) and specify that you want to copy the structure only, not structure and records.
      <LI>Open the new table in design view.
      <LI>Change the data types and set the number formats as needed.
      <LI>Close and save the table.
      <LI>Create a query based on the original table.
      <LI>Change it to an append query, and specify the copied table as target.
      <LI>Make sure that either * has been added to the query grid, or all individual fields.
      <LI>Run the query.
    In a make table query you have no influence on the resulting field types.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Table Field Format (Access 2002)

    Hi Hans

    Thanks for the speedy response (as usual) and it works fine.

    I must admit I did think of that solution, but thought there would be a way of tricking the SQL to think the fields were number fields. Still if I had thought it through a bit further I would have realised your solution is probably just as quick.


    Regards
    WTH

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

    Re: Make Table Field Format (Access 2002)

    You could probably use the Val function in a make-table query, or one of the conversion functions such as CDbl and CLng. But the method using a copy of the table and an append query offers more control over the end result.

Posting Permissions

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