MaxofDate but not today

[Simtech]

Hello,

I have created a query that uses the MaxofDate function to retrieve logbook entries created on the last day of work in our section.

This query creates a report that can be accessed from a "quick link" button on the main menu of the database. This function works great except that once a new entry is made in the logbook for "today", then the quick link button displays today's logs vice the last work day logs.

What I would like to know is if there is a way to use the MaxofDate function but have it ignore the current date. I've tried MaxofDate <Now() and MaxofDate <Date() but both of these return blank reports. Thanks for any advice you can provide.

 

[vbaInet]

I believe you're referring to the Max() aggregate function.

Let's the SQL statement of the query.

 

[Simtech]

Here is the sql for the query.

SELECT tblShiftHOData.Date, tblShiftHOData.WorkSection, tblShiftHOData.Item, tblShiftHOData.Maintenance_Software, tblShiftHOData.Entered_By
FROM tblShiftHOData
WHERE (((tblShiftHOData.Date)=[Forms]![frmShiftHOFindLatestDate]![LatestDate]) AND ((tblShiftHOData.WorkSection)="FFS" Or (tblShiftHOData.WorkSection)="CPT" Or (tblShiftHOData.WorkSection)="PCT") AND ((tblShiftHOData.Maintenance_Software)="Maintenance" Or (tblShiftHOData.Maintenance_Software) Is Null));


You will note that I am trapping the latest entry date on a hidden form and then use that to feed the query ([Forms]![frmShiftHOFindLatestDate]![LatestDate]). Perhaps not the most elegate way to get it done but the only way I could get the whole thing to work with my limited skills.

The SQL for capturing the date on the hidden from is:

SELECT Max(tblShiftHOData.Date) AS MaxOfDate
FROM tblShiftHOData;

I believe it is here were I want to try and ignore today's date. If possible.

 

[jdraw]

I'm not clear of the context of your Form(s), but your statement

SELECT Max(tblShiftHOData.Date) AS MaxOfDate
FROM tblShiftHOData;

doesn't exclude anything.

I think, in plain English, you are asking
What is the Max---read that as LATEST--Date in tableX that is less than today

SELECT MAX(yourDatefield) from TableX
Where yourDatefield < Date;

Date is a reserved word in Access,you should not use it in your table fields. Use something like MyDate or ShiftDate....

 

[Simtech]

Sorry for the lack of clarity but thank you for your patience and solution. This is what I was looking for. I have also changed the name of the Date field to avoid problems with the reserved word.

Again thanks for you solution and advice. It's much appreciated.

 

[vbaInet]

I have also changed the name of the Date field to avoid problems with the reserved word.

Fyi, here's a comprehensive list of reserved keywords:

http://allenbrowne.com/AppIssueBadWord.html