How to compare a value in Microsoft access query?-CodePudding

So I am trying to get the value total only less than 6 hours but I keep getting an error

here the query working fine:

SELECT  (B.Name),
        FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL 
FROM    ( [CHECKINOUT] AS A INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID ) 
                            INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
WHERE   A.CHECKTYPE='I' AND C.CHECKTYPE='O' AND 
        FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%' AND 
        FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%'

then by adding < to the query beside TOTAL

Select  (B.Name),FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL<6  
from(
        [CHECKINOUT] AS A
        INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID  )
        INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
where A.CHECKTYPE='I' and C.CHECKTYPE='O' AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%' AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%'

I get this error:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect".

CodePudding user response：

You can't put a filter into the SELECT part of a statement. You need to add it to your WHERE clause, e.g.

Select (B.Name),FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh') AS TOTAL
from( [CHECKINOUT] AS A 
INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID ) 
INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
where A.CHECKTYPE='I' 
  and C.CHECKTYPE='O' 
  AND FORMAT(A.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%' 
  AND FORMAT(C.CHECKTIME,'yyyy-mm-dd') LIKE ' 22-07-01%'
  AND (FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh')) < 6

I'm not 100% sure if this will work, as I don't know how Access will handle the type conversion of (FORMAT(C.CHECKTIME,'hh') - FORMAT(A.CHECKTIME,'hh')). You may have to convert it to an integer.

CodePudding user response：

You should always handle date/time as Date, not text:

Select  
    B.Name,
    DateDiff("h", A.CHECKTIME, C.CHECKTIME) AS TOTAL
From(
        [CHECKINOUT] AS A
        INNER JOIN [USERINFO] AS B ON A.USERID=B.USERID)
        INNER JOIN [CHECKINOUT] AS C ON B.USERID=C.USERID 
Where 
    A.CHECKTYPE='I' And 
    C.CHECKTYPE='O' And 
    DateValue(A.CHECKTIME) = #2022-07-01# And
    DateValue(C.CHECKTIME) = #2022-07-01# And
    DateDiff("h", A.CHECKTIME, C.CHECKTIME) < 6