By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

'Data Type Mismatch In Criteria expression' Error

P: n/a


Hi everyone

Hope someone can help with this one..

Background:

I work in vehicle fleet, I have built something to 'predict' when a
car/van service is due (it's fairly simple, calculates miles per day
between 2 known dates/mileages and then uses the service schedule to
work out what date the next service would be due, selecting the 'closer'
of 2 calculated dates, one is based on miles, one based on time).

It also calculates when the vehicles annual UK MOT (vehicle
roadworthiness) test is due and if it's within a month of the service,
ties the 2 events together.

After calculations are complete a new table is output and that is used
for subsequent reporting. The new table is overwritten each time the
calculations query is run.

Problem:

Anyway I built it for a single clients data and it worked. I have now
tried to add another client by appending their data and when the
calculation/output query runs I get the following error

'Data Type Mismatch In Criteria expression'

I thought it was because I maybe had a text field in the import that
should be a number, something like that, but copying the structure of
the main table to a temporary one and importing data to that didn't show
up any errors.

Also, the error does not always happen at the same point in the query. I
have managed to output the file with as little as 300 records appended
before the error, or as many as 600 (and points in between). If I work
in small(ish) batches to append, I seem to get further.

I thought I had a (vague) memory that date calculations can cause this
error (and I have quite a few of those), but I'm no Access expert and am
stuck for an answer.

Any help gratefully received.

Thanks in advance.

Bob
Aug 31 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Bob,
the background info is helpful, but we need more info. Are you running
a query? Could you post the SQL for it? What are the field types
involved in the joins and the where clause? Maybe they don't match and
Access isn't coercing them... So make sure they're of the right same on
both sides of the join. If you're using Autonumber on one side, the
other side would be a Long Integer.

Aug 31 '06 #2

P: n/a

Thanks for responding to my question.

The SQL is attached. I'm no expert so it's probably over complicated,
but it did work for the first 800 vehicles for one client. (BTW I use
the query builder, I'm not good enough to write raw SQL)

I just turned the make-table back into a select query, to check it that
way, and it still crashes.

Can you give me a pointer on the fundamental area of the problem? Does
that error message categorically mean that the new (additional) data I'm
using has an error? Or could it be a red herring and there's something
wrong in the way I've written the query?

Thanks

Bob



n message <11*********************@74g2000cwt.googlegroups.c om>,
pi********@hotmail.com writes
>Bob,
the background info is helpful, but we need more info. Are you running
a query? Could you post the SQL for it? What are the field types
involved in the joins and the where clause? Maybe they don't match and
Access isn't coercing them... So make sure they're of the right same on
both sides of the join. If you're using Autonumber on one side, the
other side would be a Long Integer.

Sep 1 '06 #3

P: n/a

A bit more info.

I actually deleted the source data from the db tables, merged the 2
sets of client data and reformatted them using Excel, so I had one big
vehicle file and one big history file.
Both files were formatted in line with the db field spec's (ie long
dates for dates, numbers for numbers etc).

I then re-imported the data into the empty tables in the db

The error message still occurs when the query is run.
As the db was kind of 'thrown together' at short notice but did partly
achieve it's aim for today's deadline, I think I'm going for a
re-design, it's probably quicker than trying to find the fault.

I'm still curious as to why the error occurs though

Thanks for your help.
Sep 1 '06 #4

P: n/a


Guys, thanks for taking the trouble to read this and try and assist me.

I found it..

Believe it or not, it was something really daft and the error message in
Access didn't really indicate the underlying fault.

There was 1 record where the 'last known mileage date' should have been
02/09/04 and it was 09/02/04 (2nd Sept instead of 9th Feb).

It caused the 'calculated miles per day' to come out as a negative and
blew-up a calculation that used miles per day to work out something
else.

I'm not sure how that's a 'data type mismatch' but hey, at least it's
fixed.

Thanks to all for reading and trying to assist.
Bob
Sep 1 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.