Results 1 to 6 of 6

Convert Number Format to Text in Make Table query

This is a discussion on Convert Number Format to Text in Make Table query within the Microsoft Access forums, part of the Question Forums category; I have imported data from Excel into Access, and the import spreadsheet wizard did not allow me to change the ...

  1. #1
    Board Regular Solola's Avatar
    Join Date
    Sep 2003
    Location
    Minneapolis, Minnesota
    Posts
    73

    Default Convert Number Format to Text in Make Table query

    I have imported data from Excel into Access, and the import spreadsheet wizard did not allow me to change the data type as I was importing (it was grayed out). I guess this is my first question: why did it not allow me to do this? I realize the way around this is to be sure that my data is formatted the way I want it in Excel BEFORE I import into Access, but shouldn't it allow me to change it? Why have the option there, if it's not going to let me use it? I assume I'm doing something wrong (?).

    Second question (the REAL question/purpose of this post):
    Now that my data is in Access, I need to change this field from a number format to text. I don't want to do this manually, b/c I've created a series of queries that ultimately get me the data I want. I will need to repeat this process with new data every month, so I want to just be able to import from Excel and click down the line of queries. I don't want to have to remember to manually go into the design of a table after I create it in step 7 and change the format of one field. I want to just create a field within the "make table" query (Step 7) that converts the existing number field to text. In Excel, I would just use the formula =TEXT(RID,"general").

    This does not exist in Access, so how do I do this? Is it possible?

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

    Default Re: Convert Number Format to Text in Make Table query

    First off, I think you intended to post this question to the Access forum. Maybe a moderator can move it there for us?

    Question 1:
    If importing a file from Excel, Access automatically takes whatever the format Excel has the data in. I don't think you have any ability to select/change it via the Import Wizard (believe me, I've tried)! The best way to handle it is to format the Excel file appropriately first.

    Another option, one that I have used a lot because you often get many more errors importing from Excel directly, is to save your Excel file as a Text file first, then import the Text file into Access. Then you have "control" over the format of the fields through the Import Wizard.

    Question 2:
    I would go about this a little differently. Instead of trying to change the format directly in the table, or via a Make Table Query, why not just convert the field from Numeric to Text in a query (using the FORMAT function, which is similar to Excel's TEXT function), and use the query for report/form/calculation purposes? Then you never have to worry about manipulating/massaging that field every time it comes in. Here is an example of a query calculation that will convert a Numeric entry to Text.
    Code:
    MyConvertToText: Format([Table1]![Amount],"@")
    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 Solola's Avatar
    Join Date
    Sep 2003
    Location
    Minneapolis, Minnesota
    Posts
    73

    Default Re: Convert Number Format to Text in Make Table query

    Thanks!! That will work perfectly. (what does the @ signify, though?)

    Yes, I meant to post to the Access forum - I was searching in the Access forum for an answer to my question, and then just it the button to post a question when I didn't find anything - guess I didn't do it right... Hopefully someone can move it there?

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,994

    Default Re: Convert Number Format to Text in Make Table query

    The "@" is the symbol for Text format, like "mm/dd/yyyy" is a date format and "0.00" is a number format.
    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!"

  5. #5
    Board Regular Solola's Avatar
    Join Date
    Sep 2003
    Location
    Minneapolis, Minnesota
    Posts
    73

    Default Re: Convert Number Format to Text in Make Table query

    Yeah, I suppose that's obvious...just haven't seen that before. Thanks!

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,994

    Default Re: Convert Number Format to Text in Make Table query

    Actually, I don't think it is very obvious at all (or intuitive for that matter)! I'm sure there is some sort of logic/rationale behind it, though I don't know what it is.

    The only reason I know that is because I recorded some Excel macros a while back where I was setting the format of certain cells to Text, and I noticed that the code used "@" to represent the Text format. I thought that maybe Access used the same code, and I guessed right.
    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!"

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