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

sum query: Data type mismatch in criteria expression

P: n/a
Hello!
I have created a very simple query like this:

SELECT Tasks.Name, DurationHrs([Log.DateTimeFrom],[Log.DateTimeTo]) AS
Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID;

The above query works fine. "Tasks.Name" is a "Text" field with
description of task, while "DurationHrs" is a VB function that
calculates number of hours between two "DateTime" values and returns
result of type "Single".

However, when I try to sum these hours of type "Single" in a query like
this:

SELECT Tasks.Name,
Sum(DurationHrs([Log.DateTimeFrom],[Log.DateTimeTo])) AS Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID = Log.TaskID
GROUP BY Tasks.Name;

.... I get an error "Data type mismatch in criteria expression. (Error
3464)".

What could possibly be wrong?
Thanks in advance!
Kind regards,
Igor

Jan 27 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Igor, this problem is quite common in a JET 4 database (Access 2000 and
later).

The solution is to typecast the expression in the query. Obviously this
should be superflous if the function returns a Single, but JET 4 is much
worse at understanding the data types than previous versions were.

Try:
SELECT Tasks.Name,
CSng(Sum(DurationHrs([Log.DateTimeFrom],[Log.DateTimeTo]))) AS Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID = Log.TaskID
GROUP BY Tasks.Name;

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Actually, JET 4 is also *much* slower executing user-defined function calls
too, so you might like to try:

CSng(Nz(Sum(DateDiff("n", [Log.DateTimeFrom], [Log.DateTimeTo]) / 60),0)) AS
Duration

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ig***********@infotehna.si> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hello!
I have created a very simple query like this:

SELECT Tasks.Name, DurationHrs([Log.DateTimeFrom],[Log.DateTimeTo]) AS
Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID;

The above query works fine. "Tasks.Name" is a "Text" field with
description of task, while "DurationHrs" is a VB function that
calculates number of hours between two "DateTime" values and returns
result of type "Single".

However, when I try to sum these hours of type "Single" in a query like
this:

SELECT Tasks.Name,
Sum(DurationHrs([Log.DateTimeFrom],[Log.DateTimeTo])) AS Duration
FROM Tasks INNER JOIN Log ON Tasks.TaskID = Log.TaskID
GROUP BY Tasks.Name;

... I get an error "Data type mismatch in criteria expression. (Error
3464)".

What could possibly be wrong?
Thanks in advance!
Kind regards,
Igor

Jan 27 '06 #2

P: n/a
Allen,
Now I found your answer. No need to repeat it.
Thanks!
Igor

Jan 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.