Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    1

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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 say
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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

Posting Permissions

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