Latest records - DMax problem in a report

[Guest]

Hello,

I have spent hours gathering information from this site on how to limit
records in a report to the latest only (from 'Reports' and 'Queries
sections). I have tried using DMax in a query, as suggested by Marsh, MVP
(MS Access), in the Sue thread of 2004, but I get error messages about
missing operands or invalid strings. I must be doing something wrong, and I
would be grateful for any help.
This is what I have:
I have a table of people registered for a qualification (T1) with a key text
field CandidateID, and a table of visits made to those people (T2) with a
text field CandID which joins it to T1. The tables have a one (T1) to many
(T2) relationship. I want to do a report that shows only the latest visits
for people and also all the people who have not had a visit (i.e., all
records from T1 and only the latest T2 records for those that have records in
T2).
I have entered the following in the report query as a criterion for the
visit date:
DMax(“IVdateâ€,â€[IV activity]â€,â€[CandID]=†&[IV activity].[CandID]&â€â€â€™)
where IV activity is T2, IVdate is the (date) field in T2 that I want to
limit, CandID is the (text) field in T2 that corresponds to records in T1.

The situation is more complex than shown above, because the query uses a
subquery that is based on two other subqueries, and it includes another table
that is related to T2. I wonder if the complexity is causing the problems?

I am not an expert, I am self-taught (trial and error method), so please
forgive the numerous gaps in my knowledge. Could you help me, please?

Thanks,
Ewa

 

[Roger Carlson]

If CandID is a text field, your DMax should look like this:

DMax("IVdate","[IV activity]","[CandID]='" &[IV activity].[CandID]&"'")
or
DMax("IVdate","[IV activity]","[CandID]=""" &[IV activity].[CandID]&"""")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thank you very much, that's done the trick!
Ewa

If CandID is a text field, your DMax should look like this:

DMax("IVdate","[IV activity]","[CandID]='" &[IV activity].[CandID]&"'")
or
DMax("IVdate","[IV activity]","[CandID]=""" &[IV activity].[CandID]&"""")

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

I have spent hours gathering information from this site on how to limit
records in a report to the latest only (from 'Reports' and 'Queries
sections). I have tried using DMax in a query, as suggested by Marsh, MVP
(MS Access), in the Sue thread of 2004, but I get error messages about
missing operands or invalid strings. I must be doing something wrong, and
I
would be grateful for any help.
This is what I have:
I have a table of people registered for a qualification (T1) with a key
text
field CandidateID, and a table of visits made to those people (T2) with a
text field CandID which joins it to T1. The tables have a one (T1) to
many
(T2) relationship. I want to do a report that shows only the latest
visits
for people and also all the people who have not had a visit (i.e., all
records from T1 and only the latest T2 records for those that have records
in
T2).
I have entered the following in the report query as a criterion for the
visit date:
DMax("IVdate","[IV activity]","[CandID]=" &[IV activity].[CandID]&""')
where IV activity is T2, IVdate is the (date) field in T2 that I want to
limit, CandID is the (text) field in T2 that corresponds to records in T1.

The situation is more complex than shown above, because the query uses a
subquery that is based on two other subqueries, and it includes another
table
that is related to T2. I wonder if the complexity is causing the
problems?

I am not an expert, I am self-taught (trial and error method), so please
forgive the numerous gaps in my knowledge. Could you help me, please?

Thanks,
Ewa