Select Subquery Error

[Robyn]

I copied this query from a post and modified table & field names:

SELECT T.EmpID,
(IIf([ClockIn]=True,"Clock In","Clock Out")) AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
TimeSheet AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM TimeSheet AS T;

I'm trying to use it in a query and I get the following error message:
The syntax of the subquery in this expression is incorrect. check the
subquery's syntax and enclose the subquery in parentheses.

I want to use this to calculate elapsed time. The table in which my form is
based has the following fields:

Table Name: TimeSheet
EmpID (Text)
ClockIn (Yes/No)
TimeStamp (Date/Time)

As for the Form:
Form Name: TimeSheet
TimeStamp default value: =Now()
ClockIn default value: =[Me].[Frame6].[Value]=Null

 

[Klatuu]

Without a way to test it, I am not 100% sure, but I think this is closer.

SELECT T.EmpID,
IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
IIf([ClockIn]=True, Null, DateDiff("n", (SELECT Max(Nz(Q.[TimeStamp],0)) FROM
TimeSheet AS Q WHERE Q.ClockIn = True AND Q.TimeStamp < Nz(T.TimeStamp]),0),
T.[TimeStamp])
FROM TimeSheet AS T);

 

[Robyn]

Hi: Tks. for your quick response. I added this statement in the field line
of my query and I got this error message:
The expression you entered is missing a closing parentheses, bracket or
vertical bar.

Without a way to test it, I am not 100% sure, but I think this is closer.

SELECT T.EmpID,
IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
IIf([ClockIn]=True, Null, DateDiff("n", (SELECT Max(Nz(Q.[TimeStamp],0)) FROM
TimeSheet AS Q WHERE Q.ClockIn = True AND Q.TimeStamp < Nz(T.TimeStamp]),0),
T.[TimeStamp])
FROM TimeSheet AS T);

--
Dave Hargis, Microsoft Access MVP

I copied this query from a post and modified table & field names:

SELECT T.EmpID,
(IIf([ClockIn]=True,"Clock In","Clock Out")) AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
TimeSheet AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM TimeSheet AS T;

I'm trying to use it in a query and I get the following error message:
The syntax of the subquery in this expression is incorrect. check the
subquery's syntax and enclose the subquery in parentheses.

I want to use this to calculate elapsed time. The table in which my form is
based has the following fields:

Table Name: TimeSheet
EmpID (Text)
ClockIn (Yes/No)
TimeStamp (Date/Time)

As for the Form:
Form Name: TimeSheet
TimeStamp default value: =Now()
ClockIn default value: =[Me].[Frame6].[Value]=Null

 

[Klatuu]

There is an extra right paren here:
---v
Nz(T.TimeStamp]),0)

Should be:
Nz(T.TimeStamp],0)

I don't know if there are others.
--
Dave Hargis, Microsoft Access MVP

Hi: Tks. for your quick response. I added this statement in the field line
of my query and I got this error message:
The expression you entered is missing a closing parentheses, bracket or
vertical bar.

Without a way to test it, I am not 100% sure, but I think this is closer.

SELECT T.EmpID,
IIf([ClockIn]=True,"Clock In","Clock Out") AS ClockType,
IIf([ClockIn]=True, Null, DateDiff("n", (SELECT Max(Nz(Q.[TimeStamp],0)) FROM
TimeSheet AS Q WHERE Q.ClockIn = True AND Q.TimeStamp < Nz(T.TimeStamp]),0),
T.[TimeStamp])
FROM TimeSheet AS T);

--
Dave Hargis, Microsoft Access MVP

I copied this query from a post and modified table & field names:

SELECT T.EmpID,
(IIf([ClockIn]=True,"Clock In","Clock Out")) AS ClockType,
(IIf([ClockIn]=True, Null, DateDiff("n",Nz(
(SELECT Max(Q.[TimeStamp]) FROM
TimeSheet AS Q
WHERE Q.ClockIn = True AND
Q.TimeStamp < T.[TimeStamp]),0), T.[TimeStamp]))
FROM TimeSheet AS T;

I'm trying to use it in a query and I get the following error message:
The syntax of the subquery in this expression is incorrect. check the
subquery's syntax and enclose the subquery in parentheses.

I want to use this to calculate elapsed time. The table in which my form is
based has the following fields:

Table Name: TimeSheet
EmpID (Text)
ClockIn (Yes/No)
TimeStamp (Date/Time)

As for the Form:
Form Name: TimeSheet
TimeStamp default value: =Now()
ClockIn default value: =[Me].[Frame6].[Value]=Null