Convert Number Format to Text in Make Table query

[Solola]

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?

 

[Joe4]

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.

MyConvertToText: Format([Table1]![Amount],"@")

 

[Solola]

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?

 

[Joe4]

The "@" is the symbol for Text format, like "mm/dd/yyyy" is a date format and "0.00" is a number format.

 

[Solola]

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

 

[Joe4]

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.