Max within a where clause

[kdayboll]

I'm trying to select animals that have eaten last within a date range. The FromDate doesn't matter as any feeding after that date would be fine, whether there is 1 or many. The ToDate however has to only be the most recent feedings.

And (
iif([LastFedCheck] <>0,([FromDate] <= feedings.feedingdate)
And ([ToDate] >=( SELECT Max(feedings.feedingdate)
           from feedings))

and here is the complete select statement as it will likely be needed

SELECT inventory.idcode AS ID, [inventory.genus]+" "+[inventory.species]+" "+Nz([inventory.subspecies],"") AS [Bi/Trinomial Name], IIf(inventory.sex="♂","Male",IIf(inventory.sex="♀","Female","Unknown")) AS Sex, Nz(inventory.DOB,"Unknown") AS [Birth Date], Nz(inventory.cageid,"Unknown") AS Location, inventory.feedin AS FeedIn, Max(feedings.feedingdate) AS [Last Feed], sum(
iif( 
([feedings]![feederstatus] ='Live' or  [feedings]![feederstatus] ='Frozen/Thawd' or [feedings]![feederstatus] ='Prekilled' )
,1,0
)
) AS Feedings, sum(feedings.feederqty) AS Total, inventory.image AS [Image?]
FROM ((class INNER JOIN animals ON class.class = animals.class) INNER JOIN ordertab ON class.order = ordertab.order) INNER JOIN (family INNER JOIN ((inventory LEFT JOIN feedings ON inventory.idcode = feedings.feedidcode) INNER JOIN genus ON inventory.species = genus.species) ON family.genus = genus.genus) ON ordertab.family = family.family
WHERE (
IIf([sexlookup]="Male",inventory.sex="♂",
IIf([sexlookup]="Female",inventory.sex="♀",
IIf([sexlookup]="Unknown",inventory.sex="?","")))) 
And (
IIf([birthchoice]<>"" And [birthchoice]<>"All",[birthchoice]=inventory.birth,"")
) 
And (
iif([LastFedCheck] <>0,([FromDate] <= feedings.feedingdate)
And ([ToDate] >=( SELECT Max(feedings.feedingdate)
           from feedings))

,"") 
)

And (
IIf([cagechoice]<>"Show All",[cagechoice]=inventory.cageid,"")
) 
And (
IIf([ShowOnly]="Not Eaten 5 days (CI)" Or [ShowOnly]="Current Inventory",inventory.status="Keep" Or inventory.status="Available" Or inventory.status="On Hold" Or inventory.status="Not Ready",

IIf([ShowOnly]="Sold",inventory.status="Sold",
IIf([ShowOnly]="Deceased",inventory.status="Deceased",
IIf([ShowOnly]="Perminent (Keepers)",inventory.status="Keep" Or inventory.status="On Loan",
IIf([ShowOnly]="Available",inventory.status="Available",
IIf([ShowOnly]="On Loan",inventory.status="On Loan",
IIf([ShowOnly]="Not Ready",inventory.status="Not Ready",
IIf([ShowOnly]="On Hold",inventory.status="On Hold",
IIf([ShowOnly]="No Longer in Inventory",inventory.status="Deceased" Or inventory.status="Sold" Or inventory.status="On Loan","")))))))))
) 
And (
IIf([classchoice]=" Complete Inventory","",
IIf([commonclasschoice]=" Complete Inventory","",
IIf([orderchoice]=" Show All "+[classchoice],[classchoice]=class.Class,
IIf([commonorderchoice]=" Show All "+[commonclasschoice],[commonclasschoice]=animals.commonclass,
IIf([familychoice]=" Show All "+[orderchoice],[orderchoice]=ordertab.order,
IIf([commonfamilychoice]=" Show All "+[commonorderchoice],[commonorderchoice]=class.commonorder,
IIf([genuschoice]=" Show All "+[familychoice],[familychoice]=family.family,
IIf([specieschoice]=" Show All "+[genuschoice],[genuschoice]=genus.genus,
IIf([subspecieschoice]=" Show All "+[specieschoice],[specieschoice]=inventory.species,
IIf([subspecieschoice]<>"",[subspecieschoice]=inventory.subspecies and specieschoice=inventory.species,
IIf([specieschoice]<>"",[specieschoice]=inventory.species and isnull(inventory.subspecies),[commonnamechoice]=genus.commonname)))))))))))
)
GROUP BY inventory.idcode, [inventory.genus]+" "+[inventory.species]+" "+Nz([inventory.subspecies],""), IIf(inventory.sex="♂","Male",IIf(inventory.sex="♀","Female","Unknown")), inventory.feedin, inventory.DOB, inventory.cageid;

[mrojas]

Wow; that's some SQL statement. How you tried building a query in design mode, and then setting the "date range" (I would assume you have an input form where the user specifies the dates) criteria based on form's dates input?

Could you share a database with the tables/queries you're using?

[kdayboll]

Wow; that's some SQL statement. How you tried building a query in design mode, and then setting the "date range" (I would assume you have an input form where the user specifies the dates) criteria based on form's dates input?

Could you share a database with the tables/queries you're using?

The problem is, I have so many variables. Basically this can be used for a zoo or in my case keeping track of all my reptiles and their feedings. This is why this part is so important as I can list all my babies that have not eaten yet this week and as they eat and I add the feedings, they should disappear from the list, showing me which ones are yet to eat. If I just ask the dates, they may have eaten 3 weeks ago, and not yet this week, so that would not work. I already generate the last feeding date in the original select statement, I just don't want to show any of the ones that fall after the specified date.