at most one record can be returned by this subquery error 3354

[WebDude]

Here is part of my query (a column from the query builder window)..

ChildsLocation: (SELECT "(" & IIf([HomeType] Like
"Resident*",IIf(IsNull([HomeName]),[HomeType],[HomeName]),[HomeType])
& ")" FROM ChildsLocationTable WHERE
(((ChildsLocationTable.Date)=(SELECT Max(T.Date) FROM
ChildsLocationTable As T WHERE T.[fkRequestID] = ChildsLocationTable.
[fkRequestID]))) AND Requests.pkRequestID =
ChildsLocationTable.fkRequestID)


...everything works fine unless records with identical
ChildsLocationTable.fkRequestID values also have identical
ChildsLocationTable.Date values.

What code could i add to the above subquery that would force a value
to be returned even if there is a tie for first place?...which is what
I thought MAX(T.Date) would do?


Cheers,
WebDude

 

[WebDude]

Well.. i changed the first SELECT() to SELECT MAX() and it seems to
work...though i dont understand why or what is happening..maybe it
will come to me next week.


ChildsLocation: (SELECT MAX( "(" & IIf([HomeType] Like
"Resident*",IIf(IsNull([HomeName]),[HomeType],[HomeName]),[HomeType])
& ")" ) FROM ChildsLocationTable WHERE
(((ChildsLocationTable.Date)=(SELECT Max(T.Date) FROM
ChildsLocationTable As T WHERE T.[fkRequestID] = ChildsLocationTable.
[fkRequestID]))) AND Requests.pkRequestID =
ChildsLocationTable.fkRequestID)

Cheers,
WebDude

 

[Allen Browne]

For an explanation, see:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord

 

[WebDude]

Thanks Allen