Results 1 to 5 of 5
  1. #1
    MZsarafina is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    3

    Unhappy Problem with Importing Excel DB to MS Access with text/numbers in same field

    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)
    Last edited by RuralGuy; 10-13-2017 at 11:55 AM. Reason: Fixed Title

  2. #2
    MZsarafina is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    3

    CORRECTION: Excel Importing to MS Access with text/numbers in same field

    Problem with Importing Excel DB to MS ACCESS with text/numbers in same field


  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the data type has to be text to be able to accept an alphanumeric value. You can save the values with the leading 0's, which should make it sort correctly. You can probably do it in a query too, with the Format() function:

    ?Format(1,"0000")
    0001
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    MZsarafina is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    3
    OK --I'm not familiar with queries. I'll see if I can figure out how to incorporate your formula.

    Thanks --MZ

    Quote Originally Posted by pbaldy View Post
    Well, the data type has to be text to be able to accept an alphanumeric value. You can save the values with the leading 0's, which should make it sort correctly. You can probably do it in a query too, with the Format() function:

    ?Format(1,"0000")
    0001

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In SQL view:

    ORDER BY Format(FieldName,"0000")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 12-09-2015, 01:45 PM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Replies: 7
    Last Post: 12-09-2012, 06:20 PM
  4. Replies: 6
    Last Post: 12-03-2012, 08:08 AM
  5. Replies: 3
    Last Post: 10-04-2012, 11:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums