Show last date

  • Thread starter instereo911 via AccessMonster.com
  • Start date
I

instereo911 via AccessMonster.com

Hello,

I have a query that has a date/time field on it and in the criteria field i
have Date()-1.

My problem is that on Sunday there is no data... so what I want to do is
something like

Date(show max date only).


SQL Version:
HAVING (((TotalClaimsInventory.Date)=Date()-1))


I am sure this easy but I don't know how to do it.

Thank you everyone again for your help.
 
J

Jeff Boyce

In a query (design view) you can click on the Totals toolbar button (the
"sigma") to get at the "Maximum" aggregation. This should give you a way to
find the "last" (i.e., most recent, i.e., Maximum) date in a date/time
field.

By the way, if your field is named "Date", and you tell Access to use
"Date", do you want it to use YOUR [Date] field or its own built in function
Date()? Confused? No? Access is! Change the name of your field to
something more meaningful ... "Date" is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Spencer

Perhaps what you want is

Field: YourDateField
Criteria: DMAX("YourDateField","YourTableName")

That will find all records with the latest date

If you are trying to get all the records for the latest date before today,
change the criteria to
Criteria: DMAX("YourDateField","YourTableName","YourDateField<Date()")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Jeff said:
In a query (design view) you can click on the Totals toolbar button (the
"sigma") to get at the "Maximum" aggregation. This should give you a way to
find the "last" (i.e., most recent, i.e., Maximum) date in a date/time
field.

By the way, if your field is named "Date", and you tell Access to use
"Date", do you want it to use YOUR [Date] field or its own built in function
Date()? Confused? No? Access is! Change the name of your field to
something more meaningful ... "Date" is a reserved word in Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
I

instereo911 via AccessMonster.com

Thank you both... I changed the date name and used the DMAX.. worked perfect.

I appriciate it tons

John said:
Perhaps what you want is

Field: YourDateField
Criteria: DMAX("YourDateField","YourTableName")

That will find all records with the latest date

If you are trying to get all the records for the latest date before today,
change the criteria to
Criteria: DMAX("YourDateField","YourTableName","YourDateField<Date()")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
In a query (design view) you can click on the Totals toolbar button (the
"sigma") to get at the "Maximum" aggregation. This should give you a way to
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
Top