Access SQL Query: Data Type Mismatch on TimeValue condition

[gary-bennett]

Hi all,

I've got a query (StringQuery) that returns several string fields from
a table, one of which is a string representing time (in hh:ss
format). I have another query that uses this query as its source and
which I want to filter the results based upon my time field (ie. all
records where time < x).

Here is my 2nd query:

SELECT *
FROM StringQuery
WHERE TimeValue(StringQuery.[7]) < #4:00#

When I run this new query, I see the results (and they look correct--
only the desired times are returned), but then I get the message:

"Data type mismatch in criteria expression".

I've tried creating a new query (StringQuery2) with an additional
field:

SELECT StringQuery.*, TimeValue([StringQuery].[7]) AS [Time]
FROM StringQuery;

This query populates the Time field correctly. When I execute this
query:

SELECT *
FROM StringQuery2
WHERE StringQuery2.Time < #4:00#

I get the same results: the query initially returns the correct
result set but then gives me the same data type mismatch error.

Any help you can offer would be GREATLY appreciated!

Thanks!
Gary

 

[KARL DEWEY]

Try this --
SELECT StringQuery.*, *
FROM StringQuery
WHERE (((TimeValue([StringQuery].[7])+TimeValue(#12/30/1899
0:0:1#))>TimeValue("0:04:00")));

 

[John Spencer]

Somewhere you have a null value, or a value that cannot be interpreted
as a time. When this happens you generate an error. Access may handle
this in the SELECT clause, but cannot handle it in the where clause.

Try the following and see if it will work for you.

WHERE IIF(IsDate([7]),TimeValue([7]),Null) < #4:00#

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[GeeCeeBee]

John -

That was it! Tried your WHERE statement and got the full return set
with no errors!

Thanks a million!

Gary