'Data Type Mismatch In Criteria expression' error when make-table query runs

B

bleighfield

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 car's service schedule to work out what
date the next service would be due, by selecting the 'closer' of 2
calculated dates, one is based on miles, one based on time).

It also calculates when the vehicle's 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 client's 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
 
J

John Vinson

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.

Ummm...

WHY?

You can create a Select Query returning the desired 40 records.

You can base a Report on this Query. You can base a Form on this Query
(and even update it). You can export the data in this query.

I don't see *any* benefit to the extra overhead (and the problems
you're experiencing) of creating a new Table.

Just use the Query!

John W. Vinson[MVP]
 
S

st45

Thanks for your reply...

Of course, you are correct to ask .. why?

It was because I'm no expert with Access and I had a different problem I
needed to quickly get around.

I used a 'make table' because I had no time to work out why, when I
tried to use the original 'select query' with a run-time criteria
selection on 'next service date' I kept getting an unexpected 2nd
prompt after inputting the date range.

As I was under pressure to get some output I used the create table to
get all the calculated data out and then queried that (with date
selection) for the actual reports.

Your reply did give me an idea though and I set the 'make table' back to
be a select one . The error still occurs.

If it's any help, the sql of the query is attached. I realise there's
probably a more 'elegant' way of doing this but it worked for the first
800 vehicles (from a different data source).

Maybe you could give me a pointer regarding that error message.. Does
that definitely and irrefutably mean that there is a mismatch of data
types in the underlying data? Or could something else cause it?

Thanks very much for taking the time to read this and for any help you
can offer.

Bob
 
B

BruceM

It seems you are using html e-mail format. Your SQL did not show up, at
least not in my newsreader (OE), although a couple of html tags did. In any
case, one thing to check for is null values. The Nz function may let you
avoid the error. See Help for more information about this function.
Without being able to see your SQL it is not possible to be more specific.
This is just my one thought on the matter. John Vinson will probably be
able to look at your SQL and see a lot more than I could.
 
S

st45

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top