MS Access 2007 IIF expression

30 pts.
Tags:
Access 2007 functions
IIF function
Microsoft Access 2007
Microsoft Access queries
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    Use MIN(court_date) where court_date > today_date group by client
    1,670 pointsBadges:
    report
  • carlosdl
    BaMbI05, if you post your current query we'll be able to adapt our suggestions to it.
    70,200 pointsBadges:
    report
  • BaMbI05
    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()));
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following