subquery help

  • Thread starter Thread starter Maresdd
  • Start date Start date
M

Maresdd

I get a syntax error message when trying to run a query using the following
as an expression in the query
Cat 3: IIf([Sleepover]=True,0,IIf([ServiceDate]=[PHDate],0,IIf([Category]=3
And [Hrs]>Format(2,"Fixed"),(SELECT
IIf([tblService_Date_and_Times].[End_Time] >
[tblService_Date_and_Times].[Start_Time],(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)+24,(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate),IIf([Category]=3,[Hrs],0)))))

Where have I got lost.
 
Cat 3: IIf([Sleepover]=True,0
, IIf([ServiceDate]=[PHDate],0
, IIf([Category]=3 And [Hrs]>Format(2,"Fixed"),
(SELECT IIf([tblService_Date_and_Times].[End_Time] >
[tblService_Date_and_Times].[Start_Time],
(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)+24,
(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate)
,IIf([Category]=3,[Hrs],0)))))

(SELECT IIf([End_Time] > [Start_Time]
, (([End_Time]-[Start_Time])*24)+24,
(([[End_Time]-[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate)

The subquery must return only one record and one field. Your subquery can
potentially return more than one record. Try changing the query to something
like the following.

SELECT FIRST(IIf(T.[End_Time] > T.[Start_Time]
, ((T.[End_Time]-T.[Start_Time])*24)+24,
((T.[End_Time]-T.[Start_Time])*24))
FROM tblService_Date_and_Times as T
WHERE T.Verified = True AND
T.Carer = awardquery.Carer AND
T.ServDate = awardquery.ServiceDate

Also you might consider simplifying the IIF a bit

IIF(Sleepover=True OR ServiceDate=PHDate or (Category=3 and Not Val(Hrs>2),0,
(SubQuery goes here))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
This is a problem --
And [Hrs]>Format(2,"Fixed"),(SELECT

What is this suppose to do ---- Format(2,"Fixed")
 
Maresdd -

I don't think you can have a subquery in the field expression of a query,
only in the criteria section. Can you add the tblService_Date_and_Times
table to the query? You probably need outer joins to awardquery. If you
can't get it to work, then post your entire SQL (from SQL View) into the next
posting.
 
No, a subquery is definitely a valid field expression.

Daryl said:
Maresdd -

I don't think you can have a subquery in the field expression of a
query, only in the criteria section. Can you add the
tblService_Date_and_Times table to the query? You probably need
outer joins to awardquery. If you can't get it to work, then post
your entire SQL (from SQL View) into the next posting.

--
Daryl S


Maresdd said:
I get a syntax error message when trying to run a query using the
following as an expression in the query
Cat 3:
IIf([Sleepover]=True,0,IIf([ServiceDate]=[PHDate],0,IIf([Category]=3
And [Hrs]>Format(2,"Fixed"),(SELECT
IIf([tblService_Date_and_Times].[End_Time] >
Click to expand...
[tblService_Date_and_Times].[Start_Time],(([tblService_Date_and_Times].[
End_Time]-[tblService_Date_and_Times].[Start_Time])*24)+24,(([tblService
_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24
)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate),IIf([Category]=3,[Hrs],0)))))

Where have I got lost.
Click to expand...
Click to expand...
 
thank you so much. this has done the trick. And thank you as well for the
tip. Much quicker.

John Spencer said:
Cat 3: IIf([Sleepover]=True,0
, IIf([ServiceDate]=[PHDate],0
, IIf([Category]=3 And [Hrs]>Format(2,"Fixed"),
(SELECT IIf([tblService_Date_and_Times].[End_Time] >
[tblService_Date_and_Times].[Start_Time],
(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)+24,
(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate)
,IIf([Category]=3,[Hrs],0)))))

(SELECT IIf([End_Time] > [Start_Time]
, (([End_Time]-[Start_Time])*24)+24,
(([[End_Time]-[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate)

The subquery must return only one record and one field. Your subquery can
potentially return more than one record. Try changing the query to something
like the following.

SELECT FIRST(IIf(T.[End_Time] > T.[Start_Time]
, ((T.[End_Time]-T.[Start_Time])*24)+24,
((T.[End_Time]-T.[Start_Time])*24))
FROM tblService_Date_and_Times as T
WHERE T.Verified = True AND
T.Carer = awardquery.Carer AND
T.ServDate = awardquery.ServiceDate

Also you might consider simplifying the IIF a bit

IIF(Sleepover=True OR ServiceDate=PHDate or (Category=3 and Not Val(Hrs>2),0,
(SubQuery goes here))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I get a syntax error message when trying to run a query using the following
as an expression in the query
Cat 3: IIf([Sleepover]=True,0,IIf([ServiceDate]=[PHDate],0,IIf([Category]=3
And [Hrs]>Format(2,"Fixed"),(SELECT
IIf([tblService_Date_and_Times].[End_Time] >
[tblService_Date_and_Times].[Start_Time],(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)+24,(([tblService_Date_and_Times].[End_Time]-[tblService_Date_and_Times].[Start_Time])*24)
FROM tblService_Date_and_Times
WHERE tblService_Date_and_Times.Verified = True AND
tblService_Date_and_Times.Carer = awardquery.Carer AND
tblService_Date_and_Times.ServDate = awardquery.ServiceDate
GROUP BY tblServiceDate_and_Times.Carer,
tblService_Date_and_Times.ServDate),IIf([Category]=3,[Hrs],0)))))

Where have I got lost.
Click to expand...
.
Click to expand...
 
Back
Top