locked
Saving Numbers as Numbers and Text as Text in the same Column RRS feed

  • Question

  • I have a table that contains a field called Case Number. So far all Case Number values have been a combination of numbers and text. For example:

    12-JD-7

    DC-16-00125-A

    2016CI710236

    So when I set up the table I assigned the data type for this column as Short Text. 

    Now I have a problem. I've got some new Case Number values that are long numbers like:

    168100048660

    The are getting saved as exponential notation like this:

    1.68E+11

    I tried modifying the insert query to coerce the number into a double, which works but errors for the text values. I tried saying something like IIF(ISERROR(CDBL(Case_Number)),Case_Number,CDBL(Case_Number)) but that also did not work, or maybe I did it wrong.

    Anyway, I want to save the Case Numbers exactly as they originally appear. Any help would be appreciated.

    tod

    <u5:p></u5:p>

    <u5:p></u5:p>

    Tuesday, October 11, 2016 6:45 PM

Answers

  • I dug a little deeper. The source of the data a text file. My VBA code imports the text file into a worksheet. I had a very basic statement for putting text to columns. The Case Number would would get copied into the sheet as a number. I used the macro recorder to do the text to columns process and preserve the case 'number' as text. That solved the problem. The case number stays as text when it gets imported into Access. 

    Now I'm stuck with a VERY long statement from the macro recorder, BUT that's a subject for another discussion.

    tod

    • Marked as answer by TodAtWork Friday, October 14, 2016 11:50 AM
    Friday, October 14, 2016 11:50 AM

All replies

  • Hi Tod,

    Not sure I understand the problem. If the field is Text, then why convert the number to a Double? Have you tried converting it to Text using something like the CStr() function?

    Just a thought...

    Tuesday, October 11, 2016 7:01 PM
  • The scientific notation is mostly used for display purposes where Access (and Excel) are trying to be helpful because they rather display it in this condensed version than cutting off some digits. Make the field wide enough and your problems may go away.

    -Tom. Microsoft Access MVP

    Tuesday, October 11, 2016 8:15 PM
  • Are you importing the data from Excel, by any chance?  Once the data is in Access, in a text field, it seems odd to me that it would try to display it using exponential notation, even if the value is the text representation of a long number.  But if you were importing it from Excel, I can imagine it getting converted to the text representation of a number in exponential format, somewhere along the way.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, October 11, 2016 8:24 PM
  • Hi TodAtWork,

    According to your description, do you put Access data into Excel by using VBA? If so, I have made a sample to try to reproduced this issue, unfortunately, I am not able.

    Dim xlapp As Excel.Application
    
    Dim wb As Excel.Workbook
    Dim ws As Excel.worksheet
    
    Dim rs As Recordset
    
    Set xlapp = New Excel.Application
    xlapp.Visible = True
    Set wb = xlapp.workbooks.Add
    Set ws = wb.worksheets("Sheet1")
    
    Set rs = CurrentDb.OpenRecordset("TB_Content")
    
    ws.Range("A1").copyfromrecordset rs
    
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing


    So could you provide more information about your issue, for example sample code and screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    Thursday, October 13, 2016 8:49 AM
  • I dug a little deeper. The source of the data a text file. My VBA code imports the text file into a worksheet. I had a very basic statement for putting text to columns. The Case Number would would get copied into the sheet as a number. I used the macro recorder to do the text to columns process and preserve the case 'number' as text. That solved the problem. The case number stays as text when it gets imported into Access. 

    Now I'm stuck with a VERY long statement from the macro recorder, BUT that's a subject for another discussion.

    tod

    • Marked as answer by TodAtWork Friday, October 14, 2016 11:50 AM
    Friday, October 14, 2016 11:50 AM
  • Hi Tod,

    Glad to hear you got it sorted out. Good luck with your project.

    Friday, October 14, 2016 3:20 PM