I am importing a spreadsheet from Excel - the problem is that one of the columns in excel has a currency and text format (ex. $123,000 in B5 and 3M in B6). I know in Excel you can have both formats in one column since each cell can be formatted individually - how do I replicate this in Access?
Currency and Text in same field on table
- Thread starter joyful15
- Start date
Well, I haven't done anything like that specifically, but one idea off the top would be to go to Excel with all that data in mixed format. Put it in one column, then in adjacent column, set its formatting to currency, then use formula to set the value to equal the value in first column. Hopefully, Excel will convert the text into currency, but it may not be 100% accurate, so manually check the values and fix what needs fixing.
Once you are done with that, you can reset Access's field to currency only and re-import the new values back. Be sure to include key columns so Access update the right row with right value.
Once you are done with that, you can reset Access's field to currency only and re-import the new values back. Be sure to include key columns so Access update the right row with right value.
- Local time
- Today, 18:20
- Joined
- Feb 19, 2002
- Messages
- 46,914
Keep in mind that you cannot do arithmetic with text strings and comparisons will not produce the results you will expect.
- Local time
- Today, 17:20
- Joined
- Feb 28, 2001
- Messages
- 30,038
It would be best, joyful, if you recognize that you are asking to do something that is a violation of base IT theory. I.e. you are taking the abilities of one program (Excel) and trying to make another program (Access) be something it is not.
In Excel, you think you see rows and columns. To be excruciatingly precise, you do not. They are illusions of the collections called Rows and Columns that ACTUALLY are just two-dimensional linked lists of Cells. (I'm going somewhere with this, so bear with me.) Each cell that exists at all has its own properties because the only REAL entity is a Cell. The contents of a cell are a value and a format for that value. Everything else is just smoke and mirrors. (To the rest of the forum... I've seen the internals of how you build some of the more popular spreadsheet programs. They are NOT arrays - they are sparsely linked lists, so this is NOT a totally wild guess.)
In Access, you see records with fields. The FieldDefs that are part of the table definition tell you what a record looks like. Here, there ARE no unique "cell" equivalents. A field is part of a record. Now, there ARE linked lists of whole records, but not of the fields in the record. This lack of independence means when you have two different ways of looking at things, only one way can occur naturally. Everything else has to be re-cast on the spot or done without.
Therefore, when you want currency and text properties at the same time, knowing that they are partly incompatibile, you MUST decide which way is going to be used more often and then learn how to convert that format to something else when you need it to be something else.
In Excel, you think you see rows and columns. To be excruciatingly precise, you do not. They are illusions of the collections called Rows and Columns that ACTUALLY are just two-dimensional linked lists of Cells. (I'm going somewhere with this, so bear with me.) Each cell that exists at all has its own properties because the only REAL entity is a Cell. The contents of a cell are a value and a format for that value. Everything else is just smoke and mirrors. (To the rest of the forum... I've seen the internals of how you build some of the more popular spreadsheet programs. They are NOT arrays - they are sparsely linked lists, so this is NOT a totally wild guess.)
In Access, you see records with fields. The FieldDefs that are part of the table definition tell you what a record looks like. Here, there ARE no unique "cell" equivalents. A field is part of a record. Now, there ARE linked lists of whole records, but not of the fields in the record. This lack of independence means when you have two different ways of looking at things, only one way can occur naturally. Everything else has to be re-cast on the spot or done without.
Therefore, when you want currency and text properties at the same time, knowing that they are partly incompatibile, you MUST decide which way is going to be used more often and then learn how to convert that format to something else when you need it to be something else.
I would separate the first character and then get the rest. I believe it is more flexible to have Non-Currency fields so I would simply do:
CurrencySymbol: Left([ExcelField],1)
CurrencyAmt Mid([ExcelField],2)
Simon
CurrencySymbol: Left([ExcelField],1)
CurrencyAmt Mid([ExcelField],2)
Simon
