UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Numeric Overflow On Text Field, Access 2007    
 
   
foxtrojan
post Jul 23 2019, 08:20 PM
Post#1



Posts: 1,153
Joined: 8-May 06
From: Singapore


I have a strange and frustrating error which I need your help. I use this codes "DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames" to import data from an Excel Spreadsheet to an Access Table I get a "Numeric Overflow" error on a TEXT field?
There is no Numeric data type field in my Spreadsheet. My TEXT field has no blanks. Any way to resolve this?
Go to the top of the page
 
June7
post Jul 23 2019, 08:39 PM
Post#2



Posts: 724
Joined: 25-January 16



If you want to provide spreadsheet for analysis, follow instructions at bottom of my post.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
missinglinq
post Jul 24 2019, 05:04 AM
Post#3



Posts: 4,625
Joined: 11-November 02



Can the data in the errant field of the first few rows of the spreadsheet being imported be interpreted as numeric...is it all digits, such as an account 'number' or an ID 'number?' I haven't done this kind of things in years...but I vaguely seem to remember that if in looking at the first few records Access interprets it as numeric it will be treated as such.

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
DanielPineault
post Jul 24 2019, 05:18 AM
Post#4


UtterAccess VIP
Posts: 6,765
Joined: 30-June 11



What about creating your own code to import the data?
Do you get the error if you import the file manually?
Can you provide the file for review?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
dale.fye
post Jul 24 2019, 06:57 AM
Post#5



Posts: 131
Joined: 28-March 18
From: Virginia


When working with Excel files, I generally link them rather than importing them into an existing table.

Then I run a series of tests to make sure the data is in the format I want it in (numeric, dates, foreign keys, ...). If you do this, you will likely find that Access has interpretted a column as numeric, even though you think it should be text.

The good news is that once linked, you can create a query to move the data from your linked Excel table into your Access table, and can use the conversion functions (cstr, clng, cdbl, cdate, ...) to explicitly type the data as you move it from the linked Excel table into the Access table.

HTH
Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
foxtrojan
post Jul 25 2019, 09:01 PM
Post#6



Posts: 1,153
Joined: 8-May 06
From: Singapore


Thanks June7, this is a good piece of advice. I never had a chance to learn this in the right way. Thanks mate
Go to the top of the page
 
foxtrojan
post Jul 25 2019, 09:05 PM
Post#7



Posts: 1,153
Joined: 8-May 06
From: Singapore


Thanks MissingLinq, Nice to hear from you. Yes, I will look into your advice.
Go to the top of the page
 
foxtrojan
post Jul 25 2019, 09:11 PM
Post#8



Posts: 1,153
Joined: 8-May 06
From: Singapore


Thanks Daniel, you spot on. No errors if it is done manually, by Access get external data-import method, and transfer to a new table.
I have quite a lot of Excel reports to consolidate daily, so the manual method will kill me.
Go to the top of the page
 
foxtrojan
post Jul 25 2019, 09:15 PM
Post#9



Posts: 1,153
Joined: 8-May 06
From: Singapore


Thanks Dale.fye. Sorry, my knowledge of Access/Excel is poor. Your advice is very interesting. Can you link an Excel table to a Access Table? That's real news to me.
How do you go about it.?
Go to the top of the page
 
WildBird
post Jul 25 2019, 11:18 PM
Post#10


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Are the files all the exact same format? Same field names etc? How many files are there?

You could setup some code to loop a folder to get all the files, and have a import spec setup as well and apply that so that they all come in as what you are expecting.

My approach is link to the files, append to a staging table with all text fields, and then query this for any exceptions, i.e. if any field is required, or needs to be date format etc.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
foxtrojan
post Jul 25 2019, 11:31 PM
Post#11



Posts: 1,153
Joined: 8-May 06
From: Singapore


Thanks Wildbird. Will do as advised.
Go to the top of the page
 
foxtrojan
post Jul 28 2019, 02:06 AM
Post#12



Posts: 1,153
Joined: 8-May 06
From: Singapore


Hi Missinglinq, you had the answer to my problem all along. Quote "Can the data in the errant field of the first few rows of the spreadsheet being
imported be interpreted as numeric" [even the spreadsheet data is formatted to Text, it still read as a number when imported]
Yes, and I add dummy data onto the spreadsheet, Text type, on to the first row the rest became Text. The "Numeric field Overflow" disappeared.
Thanks Missinglinq, you are great.!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 04:11 PM