Data type mismatch in criteria error (1 Viewer)

[Isskint]

I am getting this error in a query. The field generating the error is a calculated field using a custom function.
The function is:

Public Function DecimalTime(dblEvalTime As Double) As Double

DecimalTime = Hour(dblEvalTime)
DecimalTime = DecimalTime + (Minute(dblEvalTime) / 60)
DecimalTime = DecimalTime + ((Second(dblEvalTime) / 60) / 60)
DecimalTime = Round(DecimalTime, 2)

End Function

The dbalEvalTime parameter is passed in to the function as (DateIn+TimeIn)-(DateOut+TimeOut).

So the data type passed in is Double and the Function result is Double. The criteria i am applying in the query is simply <0.01. I have formatted the query field as #.00, 0.00 and General Number but it makes no difference.

I have also tried creating a second query using the first as its data source and applying the criteria in that query but still get the same error. Without the criteria the query runs fine.

 

[pr2-eugin]

Mark, why is that you have Date part passed to the function? The function does not involve Date anyway.. Also Are you passing the right fields? I mean are they all Date types?

 

[Isskint]

The function returns the decimal equivalent of the time duration so an hourly rate can be calculated.
EG 1 hour 01:00:00 = 1
30 minutes = 0.5
00:01:12 = 0.02

Yes the 4 fields being passed to the function are all date (or time) data types.

 

[pr2-eugin]

So what happens if you just pass..

returnHours: DecimalTime(TimeIn -TimeOut)

 

[gemma-the-husky]

maybe you cannot just pass a date as double

try one of these
Public Function DecimalTime(dblEvalTime As Date) As Double
Public Function DecimalTime(dblEvalTime As Date) As Date

 

[Isskint]

pr2-eugin: The DB records Order Picking allocation. It needs the date part to allow for durations across midnight. Initially i had set the DB up with one field to hold issue and another to hold receipt times (dd/mm/yy hh:mm:ss) but the client wanted to have the time and date seperate!!

gemma-the-husky: The function works fine. It is only when I add a criteria to the calculated value in a query that i get the error.

The issue only came to light when 1 picker achieved an amazing rate due to a pick time of 10 seconds. I am trying to create a query that will identify such anomalies - essentially;

SELECT tblPickLog.plID, decimaltime(([tblPickLog]![plDateBack]+[tblPickLog]![plTimeBack])-([tblPickLog]![plDateOut]+[tblPickLog]![plTimeOut])) AS PickDuration
FROM tblPickLog
WHERE (((decimaltime(([tblPickLog]![plDateBack]+[tblPickLog]![plTimeBack])-([tblPickLog]![plDateOut]+[tblPickLog]![plTimeOut])))<0.02));

 

[pr2-eugin]

What I am getting at is, since the decimalTime function has nothing to do with the Date part remove the date part passed to the function.. Just to test if something different is causing the problem..

 

[Isskint]

What I am getting at is, since the decimalTime function has nothing to do with the Date part remove the date part passed to the function.. Just to test if something different is causing the problem..

I think I understand your point, but Date is a part of this in that the value that i pass to the function is the result of the expression ((Date IN + Time IN) - (DateOut + TimeOut)). Without the date part you can not account for durations across midnight.
The reason the function only deals with Hours, Minutes and Seconds is that a Pick will not exceed 24hours (max would be 8 hours).

I do appreciate your input on this

 

[pr2-eugin]

This gets creepier.. Lol..

I just did this..

? DecimalTime((#15/07/2013#+#17:23:43#)-(#08/08/2013#+#19:43:43#))
 2.33

That is not right is it?

Also, Any chance the values might be Null/empty?

 

[Isskint]

Nope, all the dates and times are recorded automatically when pick is allocated or received back so they can not be missing. So except where a pick has not been returned (even then the data is pre filtered so that DateBack Is Not Null) all fields have a value.

In essence your little test result 2.33 would be correct as DecimalTime is only working on the time difference of 24 days 2 hours and 20 minutes.

 

[pr2-eugin]

I think I am totally clueless..

...Without the date part you can not account for durations across midnight.

Could you please help me understand this by an example?

 

[spikepl]

without date part, e.g. 22:00 follows 07:00, rather than precede it

 

[Isskint]

If you ignore the date and pass (00:25:36 - 23:58:12) i get a negative value. If i pass ((08/08/13 00:25:36) - (07/08/13 23:58:12)) i get a positive value.