Problem with query syntax

[dhess62]

This query works fine when run from Excel but when you put it into Access and run it, it gives an error on the hours statment. (see below)

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 Ideas as to what the proper syantx would be for this in Access?

 

[evanscamman]

Semi-colon at the end?

 

[stopher]

I'm pretty sure CAST is not a valid function in Access SQL.

It looks like you are trying to test if the difference between the two date/times is less than 24 hours.

Instead of the CAST expression, the following should be fine (assuming your fields are of data type Date/Time):

(current_timestamp-start_time)<1

The reason this works is that date/time is just stored as a decimal number. The value before the decimal point is the number of days and the value after the decimal point represents the portion of a day e.g. 0.75 would represent 18 hours or 18:00.

So subtracting the two date/times gives you the difference in days. Since you are interestred in 24 hours then we're talking about 1 day.

Apologies if I've laboured the point.

hth
Chris

 

[dhess62]

OK, I have the date issue solved, now all I need is to somehow get the number of tape mounts per day to show up in its own column. There is a field in the TESTSUMMARY table called NUM_TAPE_MOUNTS that needs to be used.

Here is the code I have so far.

SELECT TESTSUMMARY.START_TIME, TESTSUMMARY.END_TIME, TESTSUMMARY.LIBRARY_NAME, TESTSUMMARY.ACTIVITY, TESTSUMMARY.DRIVE_NAME
FROM TESTSUMMARY
GROUP BY TESTSUMMARY.START_TIME, TESTSUMMARY.END_TIME, TESTSUMMARY.LIBRARY_NAME, TESTSUMMARY.ACTIVITY, TESTSUMMARY.DRIVE_NAME
HAVING (((TESTSUMMARY.START_TIME) Between Date()-1 And Date()) AND ((TESTSUMMARY.ACTIVITY)="TAPE MOUNT"));

 

[Brianwarnock]

You seem to have another thread asking the same question.

Brian

 

[ByteMyzer]

Providing that NUM_TAPE_MOUNTS is a numeric field, try:

SELECT T.START_TIME, T.END_TIME, T.LIBRARY_NAME,
       T.ACTIVITY, T.DRIVE_NAME, Sum(T.NUM_TAPE_MOUNTS) AS NUM_TAPE_MOUNTS
FROM TESTSUMMARY AS T
GROUP BY T.START_TIME, T.END_TIME, T.LIBRARY_NAME,
         T.ACTIVITY, T.DRIVE_NAME
HAVING (T.START_TIME Between Date()-1 And Date())
   AND  T.ACTIVITY = "TAPE MOUNT";

 

[dhess62]

Done with this thread