Thread: Data Type Mismatch
05-23-13, 08:18 #1Registered User
- Join Date
- May 2013
Unanswered: Data Type Mismatch
Hi all.. new here, so forgive me if I don't sum my problem up well.. I know that this question has probably been done to death, but depsite my best efforts of googling, I am not coming up with a solution.
I am also relatively new at using Access in this way...
I have a database set up with linked tables from an Oracle SQL server. The database then runs various queries from them, and populates a table that aggregates with some other data that is manually updated into the tables.
This bit is working fine - so I am pretty confident that the issue doesn't relate back to the data that is being pulled in from elsewhere.
I then have another query that uses data from the aggregate table and appends a load of calculated columns to it then writes it into another table (with the data tyeps already set up).
For example, from a date in the row, it tells you whether it was This Week, Last Week, 4 weeks ago, 8 weeks ago..
I run this every week and each week, new rows of data are appended, and the columns are recalculated for EVERY row in the table.
This has been running fine for several months, but this week I have the Data Type Mismatch error message.
My BIGGEST problem is that this query outputs around about 1.1m rows of data, so identifying the problem is causing me an issue. I am able to filter it down to the new rows that are being added this week, but I just can't see what could be causing the error message - everything looks right.
Any suggestions on what I could try to be able to create this table would be greatly appreciated.
05-23-13, 09:17 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
data mismatch usually means that the Access runtime is expecting a specific type of value and is instead getting one in cannot coerce into the expected type.
it could be a data error, or it could be a problem with the way the data is being received from the source
the most common is
a string in place of a numeric (eg 'NULL' as opposed to null)
an invalid date, or a date format the setup doesn't recognise
the problem sounds like its going to be in the aggregate query
diagnosing the fault could be tricky, especially if its a data error. if the data ison your local machine then I'd run versions of the query till you identify the column that iw throwing the error
say your query has 8 output columns
copy the query to a new query, delete 4 columns from the output, if you get an error then reinstate the 4 columns, and drop to of the ones from the first iteration
keep going to you find which column throws the error
my guess is that you are getting a null value when you shouldn't
or corrupt/invalid date
or a space where you shouldn't
without seeing your query and the underlying datatypes its hard to sayI'd rather be riding on the Tiger 800 or the Norton
05-23-13, 22:49 #3Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
Do these Fields include any 'Year-to-Date' type of thing? A common mistake is in assigning a Field Size to a Number Field that is too small to hold the data. If you have a Field defined as Number/Integer, everything will be fine until the Value exceeds 32K (the limit for an Integer.)
Y-T-D Fields often trigger this problem. They hold the values until the Y-T-D exceeds the limit, then pops an error. By rights, this should not be a '13 type mismatch' error, as it is still a Number Field, but I have seen it do this before.
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