Latest records - DMax problem in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Thank you very much, that's done the trick!
Ewa

Roger Carlson said:
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

Ewa said:
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
Click to expand...
Click to expand...
 
Back
Top