- Joined
- Jun 21, 2006
- Messages
- 1
- Reaction score
- 0
Hi, I am trying to create a query that pulls the last record for a particular date - the query is triggered currently by two parameters (startdate and enddate) - if no record is found within those dates, it should bring back the last date for which it has a record. The below brings back no records (although it runs) and I'm very, very confused!!!
I've also tried to run the IIF part using <[@startdate] to test the condition, but I get the same result (ie no records)
SELECT DISTINCTROW tblMonVal.MVID, tblMonVal.MVAssetID, tblMonVal.MVAssetPX, tblMonVal.MVDate, tblMonVal.MVUSDVal, tblMonVal.MVAccInt, tblMonVal.MVTotAcc
FROM tblMonVal
WHERE ((((([tblMonVal].[MVDate]) Between [@startdate] And [@enddate] Or IIf(ISNULL([tblmonval].[mvdate]),([tblMonVal].[MVDate])=(Select max([subval.mvdate]) from tblmonval as subval where subval.mvid = tblmonval.mvid))),0)<>False))
GROUP BY tblMonVal.MVID, tblMonVal.MVAssetID, tblMonVal.MVAssetPX, tblMonVal.MVDate, tblMonVal.MVUSDVal, tblMonVal.MVAccInt, tblMonVal.MVTotAcc;
I've also tried to run the IIF part using <[@startdate] to test the condition, but I get the same result (ie no records)
SELECT DISTINCTROW tblMonVal.MVID, tblMonVal.MVAssetID, tblMonVal.MVAssetPX, tblMonVal.MVDate, tblMonVal.MVUSDVal, tblMonVal.MVAccInt, tblMonVal.MVTotAcc
FROM tblMonVal
WHERE ((((([tblMonVal].[MVDate]) Between [@startdate] And [@enddate] Or IIf(ISNULL([tblmonval].[mvdate]),([tblMonVal].[MVDate])=(Select max([subval.mvdate]) from tblmonval as subval where subval.mvid = tblmonval.mvid))),0)<>False))
GROUP BY tblMonVal.MVID, tblMonVal.MVAssetID, tblMonVal.MVAssetPX, tblMonVal.MVDate, tblMonVal.MVUSDVal, tblMonVal.MVAccInt, tblMonVal.MVTotAcc;