last record

[JB]

Hello
I want to create a report that shows only the last dated record in a table.

Report fields:
ActivityID
Activity Date
Activity Notes
ClientName
Consultant

I want the report to only show the last Activity entered and not all of
them.
Please can you advise.
Ta
Jen

 

[Steve]

Create a quey based on your table. Convert the query to a totals query by
clicking on the Sigma (looks like a capital E) button in the menu at the top
of the screen. Under the ActivityDate field, use the drop down list change
Group By to Max. Your query qill return only the last dated record. Base
your report on this query.

Steve

 

[BruceM]

You can use the Where condition of OpenReport:

Dim datLatest as Date
datLatest = DMax("[DateField]","[YourTable]")

DoCmd.OpenReport "ReportName", acViewPreview, , _
"[CAR_Date] = #" & datLatest & "#"

The underscore (line continuation character) is optional.

If there are several records with the same Max date you will need to do
something more to specify the record you want.

 

[BruceM]

May I suggest you limit this type of response to when Steve acutally
solicits money? There are plenty of opportunities for that. You may be
sending an unintended message to the OP or to new uses of this group that
only responses of a certain caliber are welcome, but in fact all
non-commercial responses are welcome. If somebody posts inaccurate advice
somebody else will likely set the record straight, but people should not
fear reprisals for weighing in with their suggestions and opinions.

 

[JB]

Thanks everyone. Will try all suggestions.

You can use the Where condition of OpenReport:

Dim datLatest as Date
datLatest = DMax("[DateField]","[YourTable]")

DoCmd.OpenReport "ReportName", acViewPreview, , _
"[CAR_Date] = #" & datLatest & "#"

The underscore (line continuation character) is optional.

If there are several records with the same Max date you will need to do
something more to specify the record you want.

Hello
I want to create a report that shows only the last dated record in a
table.

Report fields:
ActivityID
Activity Date
Activity Notes
ClientName
Consultant

I want the report to only show the last Activity entered and not all of
them.
Please can you advise.
Ta
Jen

 

[JB]

Hi Steve.
I did what you said but unfortunately it didn't work.
Also I think I should have cleared up that I don't just want the last
activity overall but the last one for each Client.

When I tried what you said, it still showed all the records, but sorted by
the Activity Date.

Jen

 

[Steve]

In the Totals query only include the ActivityID and ActivityDate fields.
Create another query and include the Totals query and your query or table
that has the report fields you want. Join on ActivityID. Now base your
report on this new query.

Steve