Last Date

[Linda RQ]

Hi Everyone,

I have a query in Access 2003 and I want to only show the records from my
ThpyEndDtTm field that are the most recent. I think I need to use the DLast
function and I have been trying to learn to use the builder tool since I
took a class and they were using this. I was able to follow along well in
class but couldn't get the notes too, it seemed easy at the time <g> but the
teacher knew exactly what needed to be in the parentheses and what didn't.

Here is where I am at.....In my query grid, I R click on my field
ThpyEndDtTm and select build. I then click on the built in functions and
navigate to the DLast function. When I double click that, I get this

ThpyEndDtTm «Expr» DLast («expr», «domain», «criteria»)

I think I am supposed to delete this <<Expr>> and cut the ThpyEndDtTm and
paste it in here..... <<expr>>. I don't know what to do with domain or
criteria. I tried several variations and get "not enough argument" errors
and other various errors and thrashings.

Thanks,
Linda

 

[John W. Vinson]

Hi Everyone,

I have a query in Access 2003 and I want to only show the records from my
ThpyEndDtTm field that are the most recent. I think I need to use the DLast
function and I have been trying to learn to use the builder tool since I
took a class and they were using this. I was able to follow along well in
class but couldn't get the notes too, it seemed easy at the time <g> but the
teacher knew exactly what needed to be in the parentheses and what didn't.

Actually, DLast() is misleading and durn near useless. It does not return the
most recent record; it certainly does not return the most recent date in a
date field; it returns the last record *in disk storage order* - and Access
will store the records on disk in whatever order it finds most convenient.
Therefore DLast() returns an arbitrary, uncontrollable record. :-{(

Here is where I am at.....In my query grid, I R click on my field
ThpyEndDtTm and select build. I then click on the built in functions and
navigate to the DLast function. When I double click that, I get this

ThpyEndDtTm «Expr» DLast («expr», «domain», «criteria»)

I think I am supposed to delete this <<Expr>> and cut the ThpyEndDtTm and
paste it in here..... <<expr>>. I don't know what to do with domain or
criteria. I tried several variations and get "not enough argument" errors
and other various errors and thrashings.

If you want to find the *maximum* value - the most recent - of a Date/Time
field you can use the DMax() function. In order to have a form display the
most recent record in the table, you need to base the form on a Query using a
DMax() call as a criterion on ThpyEndDtTm. Create a query based on your table,
containing all the fields that you want to see; on the Criteria line under
ThpyEndDtTm type

=DMax("[ThpyEndDtTm]", "[YourTableNameHere]")

to find the most recent date in the entire table. If you wnat to find the most
recent ThpyEndDtTm for each of a class of records (patients perhaps???) you
can put an optional third parameter in the DMax call, selecting the subset of
records for which you want the maximum:

=DMax("[ThpyEndDtTm]", "[YourTableNameHere]","[PatientID]= " & [PatientID])

This uses the PatientID fieldname in two contexts: inside the quotes, it
specifies which field in YourTableNameHere will be searched; outside the
quotes it specifies what value to search for (so for PatientID 315, it will
find the most recent date for [PatientID] = 315).

John W. Vinson [MVP]

 

[Guest]

Linda:

As you want to restrict the query to those rows with the latest date
(presumably per patient) you don't actually want to use an expression which
calls a domain function, but the MAX function. If you simply want to show
the patients and each one's latest ThpyEndDtTm date then you can group the
query by patient and return the MAX(ThpyEndDtTm). In design view you'd add
fields like PatientID, FirstName, LastName and ThpyEndDtTm to the design
grid, select Totals from the View menu and in the 'Totals' row of each column
in the design grid leave all but ThpyEndDtTm as 'Group By' but select 'Max'
for the ThpyEndDtTm column.

BTW be sure to include a guaranteed unique value per patient, PatientID
above, in the grouping, not just the names. I once attended a clinic where
two other patients attending on the same day had identical first and last
names and identical dates of birth. If in your case you had two patients
with the same names and you didn't include a unique PatientID you'd get the
patient whose ThpyEndDtTm date was the latest and the other patient would
not be returned by the query at all.

If you want to return other columns than those on which you can group, i.e.
you want to include columns which don't have the same value in all rows for
each patient, then it’s a bit trickier. You either have to first create a
'totals' query like the above, then join that to your main table on the
patientID and date fields to produce the final query, or you can use a single
query with a subquery, but that, or at least the subquery part of it, has to
be written in SQL.

Ken Sheridan
Stafford, England

 

[Linda RQ]

Thanks John and Ken. I played around for a few days and I finally got
things to workout.

Linda