HELP PLEASE – I KNOW THIS IS LONG, BUT REALLY COULD USE SOME ADVISE.
Problem with Importing Excel DB to MS ACCESS with text/numbers in same field
You know how in Excel a cell can be treated as a “General number”, Currency” or let’s say “Text”? In our database, the Bldg # field contains “numbers” and sorting and exporting is very simple.
HOWEVER, when a Building Number contains an “alpha” character (i.e. 0045E), that number field is changed to “text”, and when sorting the Excel data, Numbers are sorted first, then followed by “text. Therefore, Bldg #’s with an alpha, are placed at the very end.
-Now importing the Excel DB to MS Access:
Taking this same Excel database and importing into the MS Access, you have to identify from the available choices, what type of data is each field. I’ll only talk about these two pertaining to the Bldg # field:
-Short text (any text, numbers, characters) displayed as is.
-Number: Just numbers
If I choose the Bldg # field as “short text”, all the imported numbers are treated as “Text” and sort this way:
Bldg #
1
100
1202
2
200
2463
Not like this as expected:
Bldg #
1
2
100
200
1202
2463
IF I CHOOSE the Bldg # field as “Number”, they sort perfectly, EXCEPT that the Bldg # with an alpha is REMOVED because it is not considered a number. Blank field.
If I type in the Bldg # at that point, MS Access changes the entire Bldg # column to TEXT.
Dang it. I’M GOING CRAZY !!!
Oh, by the way, when the field is a number, that’s when I can automatically mask the field it to always have leading zeros (Bldg # 1 becomes Bldg # 0001)