Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Numeric Field Overflow (2002)

    Hi,

    I have an Access database that is importing information into from an excel file. This was initially working fine. Now I'm getting an error that states Numeric Field Overflow. I've verified that fields are marked correctly in the database either as text or date fields. Text fields are set as 255 and Date fields are set to short date. The user is forever typing in text in date fields and in the past Access would simply state that some fileds hadn't been imported. She's not even getting that error. I went in and checked each of the fields in the excel sheet to see if there was anything obvious and cleaned up what I could, but am now stumped. She had some dates entered as 00/00/00 and I took those out. I've attached the excel sheet.

    Thanks,
    Alicia

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Numeric Field Overflow (2002)

    Where is the excel sheet?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    Sorry! A Daaaaaaaaaa Moment!

    Leesha
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    Jeeze! I responded to myself twice. What a day!

  6. #6
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numeric Field Overflow (2002)

    My first suspicion of the culprit would be the Drivers Licence # field. That needs to be a text field (due to some entries near the bottom). If it got picked up by Access as an Integer that would explain the overflow for most records.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    Hi,

    I had already checked that and it was and a text propety. Interestingly, when I was stripping down the file to make it smaller so that I could upload it, I found that if the table is included in the front end of the database vs in the split / backend tables, I don't get the error. It's only when I try to import into the backend tables. I don't understand what would cause this.

    Leesha

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Numeric Field Overflow (2002)

    What process do you use for the import?
    Do you have code that processes each line? Do you import to a new table, or directly into an existing one?
    Regards
    John



  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    Hi John,

    This is the code that I use. A query deletes the previously imported data.


    'Deletes previously imported files from tblStaffListingReport

    DoCmd.OpenQuery "qryDeletetblStaffListing"


    'Imports all excel files residing in designated folder

    Dim strFolder As String
    Dim strFile As String


    strFolder = "C:HR DatabaseSuncoast File"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblStaffListingReport", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="Additional!"
    strFile = Dir
    Loop

    'Gives message that import is complete

    MsgBox "Import Complete"


    Exit Sub

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Numeric Field Overflow (2002)

    Hi Leesha, long time no see.

    What does the following code do:
    Range:="Additional!"

    Have you found the problem yet?

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Numeric Field Overflow (2002)

    In Excel a Range defines a part of the spreadsheet. So here it defines which part of the spreadsheet to import.

    Here is some more about them.
    Regards
    John



  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numeric Field Overflow (2002)

    HI Pat!

    Yes its been awhile. I've been busy in ASP.net!

    "Additional!" is the name of the sheet in the workbook that gets pulled into the database. There are two other sheets which I deleted just in case that was the issue but it wasn't. I've been using this snippet of code for about 3 years in various databases. Hans gave me the code and it works great. I "believe" this is the first time I've used it in a database with linked tables so I'm no sure if that has anything to do with it. It doesn't seem to me that it should make a difference but it seems to. It works fine if the table isn't linked. I get the numeric overflow error when the table in linked.

    Leesha

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Numeric Field Overflow (2002)

    I don't get an overflow error if I import your sheet into Access, but i do get other import errors:
    - Cell N172 contains a non-existant date 02/29/09 (2009 is not a leap year, so no February 29)
    - Cell O64 contains a text string while all other cells in that column contain numbers.
    - Cells T50:T54 contain text values while all values above them look numeric.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •