Access 2000: Data Type Mismatch in query

[Arvi Laanemets]

Hi

I have a simple query, which retrieves a couple of fields from linked Excel
table and calculates some values - something like this:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) ORDER BY 2;

So long it works fine. But I need some additional conditions here. Really I
want to display rows, for which calculated field BirthDay was between
previous and next workdays from current date, but whenever I try to
construct such condition, I'll get "Data type mismatch in criteria
expression" error. In criteria are compared integer expressions calculated
from BirthDate field and from current date with integer constants.

It looks, like any calculations in WHERE clause, based on dates, will return
this error (the original expression is too long, so I present more simple
one as example - I tested it too). P.e. the error is returned by:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) AND
(DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))>50)
ORDER BY 2;

But when I set addidional condition based on non-date source values, the
query works OK - like this:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) AND
(Len[MyTable].[Name]>15) ORDER BY 2;

I have tried to wrap expressions into CInt() or CLng() or Int() functions,
but wihout any luck. Can someone explain, what is wrong here.
PS. Dates in Excel table are in format dd.mm.yyyy, but the query reconizes
them as dates without any problems and all date functions work OK, except in
WHERE clause.
PS. I tried with a secondary query, with first one without additional
conditions as source, and setting conditions to fields BirthDay or Age there
resulted as same error too.

Thanks in advance

 

[Tom van Stiphout]

What the Excel rows are formatted as makes no difference. My guess is
that you have many rows and that some of them represent illegal dates.
Strip the file down to 10 rows, and try again.

I've never seen "ORDER BY 2": what is that? "2" is not a column name,
right?

-Tom.

Hi

I have a simple query, which retrieves a couple of fields from linked Excel
table and calculates some values - something like this:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) ORDER BY 2;

So long it works fine. But I need some additional conditions here. Really I
want to display rows, for which calculated field BirthDay was between
previous and next workdays from current date, but whenever I try to
construct such condition, I'll get "Data type mismatch in criteria
expression" error. In criteria are compared integer expressions calculated
from BirthDate field and from current date with integer constants.

It looks, like any calculations in WHERE clause, based on dates, will return
this error (the original expression is too long, so I present more simple
one as example - I tested it too). P.e. the error is returned by:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) AND
(DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))>50)
ORDER BY 2;

But when I set addidional condition based on non-date source values, the
query works OK - like this:
SELECT [MyTable].[Name],
DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate]))
AS BirthDay,
DateDiff("yyyy",[MyTable].[BirthDate],DateSerial(Year(Date()),Month([MyTable].[BirthDate]),Day([MyTable].[BirthDate])))
AS Age FROM MyTable WHERE (MyTable.ID Is Not Null) AND
(Len[MyTable].[Name]>15) ORDER BY 2;

I have tried to wrap expressions into CInt() or CLng() or Int() functions,
but wihout any luck. Can someone explain, what is wrong here.
PS. Dates in Excel table are in format dd.mm.yyyy, but the query reconizes
them as dates without any problems and all date functions work OK, except in
WHERE clause.
PS. I tried with a secondary query, with first one without additional
conditions as source, and setting conditions to fields BirthDay or Age there
resulted as same error too.

Thanks in advance

 

[Arvi Laanemets]

Hi

What the Excel rows are formatted as makes no difference. My guess is
that you have many rows and that some of them represent illegal dates.
Strip the file down to 10 rows, and try again.

Excel tables are queries from DBF tables (3rd-party program). VBA script
refreshes them at every night. And with illegal dates, there would be errors
in calculated fields, but they are all OK. But I'll give it a try tomorrow -
my workday is over for an hour now.

I've never seen "ORDER BY 2": what is that? "2" is not a column name,
right?

The column number, i.e. in my example 2. column in query table (BirthDay).
It is especially useful, when you want to sort by calculated field with
complex formula - otherwise you have to repeat this formula in ORDER BY
clause. I don't remember, is this syntax described somewhere in VBA help or
not - I myself learned it years ago when designed databases in FoxPro - but
it works fine.