Query not working between Excel and Access

[dhess62]

When I run this query from Excel it works fine. When I build a query in
Access and try to run it, it will error out saying "missing operator in query
expression 'activity'='TAPE_MOUNT' and
CAST((current_timestamp-start_time)hours as decimal) <= 24 group by
drive_name. I'm not sure why this is happening.

QUERY:
select distinct drive_name,count(*) as NUM_TAPE_MOUNTS from summary where
activity='TAPE MOUNT' and CAST((current_timestamp-start_time)hours as
decimal) <= 24 group by drive_name

Any help would be greatly appreciated,

 

[John Spencer]

Access SQL does not recognize CAST function.

You might want to use the DateDiff function
DateDiff("h",Start_Time,Current_Timestamp)< 24

I assume that the Start_Time and Current_TimeStamp have a date and a
time. Otherwise this makes little sense, because with just times you
would never get a value more than 23.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[dhess62]

Thanks, I'll give that a try

Access SQL does not recognize CAST function.

You might want to use the DateDiff function
DateDiff("h",Start_Time,Current_Timestamp)< 24

I assume that the Start_Time and Current_TimeStamp have a date and a
time. Otherwise this makes little sense, because with just times you
would never get a value more than 23.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================