Access Query Criteria

[kooley]

I have a Query that includes a field called "Check Date". I need to have criteria that tells the DB, only give me records that do not have any check dates greater than or equal to a specified date. I have tried using the MAX sort function with "<=(Date()- 120) or IsNull". The problem is that it continues to pull records to the results that have check dates after the specified date. We are using this to find inactivity in a database.

 

[AlexCuse]

Take a look at the DateAdd function in help files.

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[kooley]

I tried adding the "DateAdd" function within my query & it's not what I need. I have now tried writing a query with the "account type" & "% complete" criteria. Then I have created a query based on that query. Within this final query, I have placed my Max Ck Date field using the following formula:
Max Ck Dte: MAX([Inactivity Rpt Query]![Check Date])

Now I keep getting a message that says: "Cannot group on fields selected with '*'."

I have searched for this in the Help screens and nothing comes up. If I remove the above formula & just look at "Check Date", I do not get this message. Any ideas?

Thanks for your help.

 

[dhookom]

Could you share the SQL view of your query? Do you have the query properties set to display all fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kooley]

I do have the properties set to display all fields.
Here is the SQL view for the 1st query:

SELECT CUSTOMER.[Account Number], CUSTOMER.[Borrower Last Name], Disbursements.[LIP or Savings Account], Disbursements.[Check Date], Disbursements.[Amount Disbursed], CUSTOMER.[% Complete], CUSTOMER.Comments
FROM CUSTOMER RIGHT JOIN Disbursements ON CUSTOMER.[Account Number] = Disbursements.[Account Number]
WHERE (((Disbursements.[LIP or Savings Account])="lip" Or (Disbursements.[LIP or Savings Account])="savings") AND ((CUSTOMER.[% Complete])<100));

This is the SQL View for the query running off of the 1st query:

SELECT [Inactivity Rpt Query].[Account Number], [Inactivity Rpt Query].[Borrower Last Name], [Inactivity Rpt Query].[LIP or Savings Account], Max([Inactivity Rpt Query]![Check Date]) AS [Max Ck Dte], [Inactivity Rpt Query].[Amount Disbursed], [Inactivity Rpt Query].[% Complete], [Inactivity Rpt Query].Comments, *
FROM [Inactivity Rpt Query];


Hopefully you can see something that will solve my dilema. The logic seems so simple, but sometimes that can be the most difficult.

Thanks in advance

 

[dhookom]

You can't use "Max(...)" without specifying a GROUP BY. If you want to use Max(), you must change the query to a totals query and remove the display all fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Thread701-1258387 has an explanation of the GROUP BY clause that you may find helpful.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[kooley]

Thank you everyone for all of your suggestions! I was finally able to get my expected results using 2 separate queries that then combined into a final query for the report to run on. This is the best site for ideas & help!
Happy Holidays to all!!