MS Access 2007 IIF expression
I have multiple court dates tied to a client. I have a query created to pull the records. Right now, it's pulling all records with all court dates that is greater than today's date. I want the report to show just the next court date for each client. Is there an expression can I use to show only the next court date?
Software/Hardware used:
MS Office 2007
Software/Hardware used:
MS Office 2007
ASKED:
October 8, 2010 8:17 PM
UPDATED:
October 20, 2010 5:00 PM
Answer Wiki:
I finally got it to work. I had to create an initial query to pull all court dates greater than today's date. Then I tie that query into another query to pull just the first court dates and that gave me the correct # of records.
thank you to everyone for their assistance.
To see all answers submitted to the Answer Wiki: View Answer History.
Use MIN(court_date)
where court_date > today_date
group by client
BaMbI05, if you post your current query we’ll be able to adapt our suggestions to it.
Sorry it took so long. So I’ve tried what was suggested by Msi77, but I noticed that it’s not pulling up every record. I should have at least 115 records with at least one court date greater than today’s date, but this is only pulling up 89 records. Is there something that I look for to make it pull up all data that has a court date greater than or equal to today’s date?
This is the sql view of the query:
SELECT tblCourt.OffenderID, Min(tblCourt.CourtDate) AS MinOfCourtDate, tblCourt.CourtTime
FROM tblCourt
GROUP BY tblCourt.OffenderID, tblCourt.CourtTime
HAVING (((Min(tblCourt.CourtDate))>=Date()));