subquery help

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.
 
J

John Spencer

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
 
K

KARL DEWEY

This is a problem --
And [Hrs]>Format(2,"Fixed"),(SELECT

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

Daryl S

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.
 
B

Bob Barrows

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...
 
M

Maresdd

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#Error in calculated field 3
run a make table query using parameters in multi-select list box 7
query comes up blank 1
select or dsum??? 3
Invalid syntax in dlookup 4
SUBQUERY BLUES 8
selecting records between 2 dates 6
Subquery not integrating to query to display previous sum of quantities 3

Top