Problem with criteria on DateDiff calc field 1

[LakotaMan]

Am using version 2007 and need to evaluate the difference in 2 fields. I created a query that gives the interval in hours between 2 datetime fields. I then used this query in another one. Needing to isolate those that are less than 16 hours, I used <16 as my criteria. As below:

SELECT qry15MinByPassTimes.ysnUnder15Mins, qry15MinByPassTimes.Duration
FROM qry15MinByPassTimes
WHERE (((qry15MinByPassTimes.Duration)<16))

Acccess keeps giving me the "Data type mismatch in criteria expression" message.

This is the first time I've had this happen, and have used DateDiff successfully in the past. Am pullling my hair out, so any comments or help will be greatly appreciated.

LakMan

 

[dhookom]

I am having trouble seeing the SQL of qry15MinByPassTimes. I expect Duration may be either a string or null.

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Sorry, here's that one:

SELECT tblEquipmentMalfunction.EquipmentMalfunctionID, tblEmissionsControlEquipment.ysn15MinByPassMonitoring, tblEquipmentMalfunction.ysnUnder15Mins, CDate(Format([dtmDateMalfunctionIndicated] & " " & [dtmTimeMalfunctionIndicated],"General Date")) AS DateTimeAlarm, CDate(Format([dtmDateFixed] & " " & [dtmTimeFixed],"General Date")) AS DateTimeFixed, DateDiff("n",[dateTimeAlarm],[DateTimeFixed]) AS Duration
FROM tblEquipmentMalfunction INNER JOIN tblEmissionsControlEquipment ON tblEquipmentMalfunction.MalfunctioningEquipment = tblEmissionsControlEquipment.EmissionsControlEquipmentID
WHERE (((tblEmissionsControlEquipment.ysn15MinByPassMonitoring)=Yes))

 

[dhookom]

If [dtmDateMalfunctionIndicated] and [dtmTimeMalfunctionIndicated] are both date/time values, you should be able to simply add them together to get the date and time.

[dtmDateMalfunctionIndicated]+[dtmTimeMalfunctionIndicated] As DateTimeAlarm

I try to avoid using a calculated alias in another calculated column in a query.

When you view the above query, is the Duration field displayed left or right aligned? If it is left aligned, wrap it in Val()

Val(DateDiff("n",[dateTimeAlarm],[DateTimeFixed])) AS Duration

This would have been my first attempt at your query:

SELECT tblEquipmentMalfunction.EquipmentMalfunctionID, tblEmissionsControlEquipment.ysn15MinByPassMonitoring, 
tblEquipmentMalfunction.ysnUnder15Mins, 
[dtmDateMalfunctionIndicated] + [dtmTimeMalfunctionIndicated] AS DateTimeAlarm, 
[dtmDateFixed] + [dtmTimeFixed] AS DateTimeFixed, 
DateDiff("n",[dtmDateMalfunctionIndicated] + [dtmTimeMalfunctionIndicated],[dtmDateFixed] + [dtmTimeFixed]) AS Duration
FROM tblEquipmentMalfunction INNER JOIN tblEmissionsControlEquipment ON tblEquipmentMalfunction.MalfunctioningEquipment = tblEmissionsControlEquipment.EmissionsControlEquipmentID
WHERE (((tblEmissionsControlEquipment.ysn15MinByPassMonitoring)=Yes))

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,

I replaced my SQL with yours, added the <16 Duration criteria, and it works like a charm.

I'm not good enough at SQL to see the difference between the two straight off, but I intend to study it.

Thanks, as always, for your help.

LM