DMAX on UPDATE with INNER JOIN

[Anne]

I would like to take the most recent Acquired date for each Emp ID from the
Assigned table for a specific certification and place it in the
EAPAnniversary field in the Status table. I have this for the query:

UPDATE tblAssigned INNER JOIN tblStatus ON tblAssigned.[Emp ID] =
tblStatus.[Emp ID] SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

But I get a type conversion error. I tried to use DateValue around the DMax
and got the same error. It worked when I did not have criteria in the Dmax,
but of course everyone got the Max date across the domain, not just for
themselves. Both the Acquired and EAPAnniversary fields are Date/Time types.

I'm using Access 2003. Any help appreciated.

 

[louisjohnphillips]

I would like to take the most recent Acquired date for each Emp ID from the
Assigned table for a specific certification and place it in the
EAPAnniversary field in the Status table.  I have this for the query:  

UPDATE tblAssigned INNER JOIN tblStatus ON tblAssigned.[Emp ID] =
tblStatus.[Emp ID] SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

But I get a type conversion error.  I tried to use DateValue around theDMax
and got the same error.  It worked when I did not have criteria in the Dmax,
but of course everyone got the Max date across the domain, not just for
themselves.  Both the Acquired and EAPAnniversary fields are Date/Time types.

I'm using Access 2003.  Any help appreciated.

Is it possible the

DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])


should be modified to

DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus].[Emp ID])

That is changing the exclamation point to a period.

 

[John Spencer]

UPDATE tblAssigned INNER JOIN tblStatus
ON tblAssigned.[Emp ID] = tblStatus.[Emp ID]
SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=""" & [tblStatus]![Emp ID] & """")
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

If EmpId is a text field then try the above.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Anne]

Thanks for the reply. Unfortunately, same error.

I would like to take the most recent Acquired date for each Emp ID from the
Assigned table for a specific certification and place it in the
EAPAnniversary field in the Status table. I have this for the query:

UPDATE tblAssigned INNER JOIN tblStatus ON tblAssigned.[Emp ID] =
tblStatus.[Emp ID] SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

But I get a type conversion error. I tried to use DateValue around the DMax
and got the same error. It worked when I did not have criteria in the Dmax,
but of course everyone got the Max date across the domain, not just for
themselves. Both the Acquired and EAPAnniversary fields are Date/Time types.

I'm using Access 2003. Any help appreciated.

Is it possible the

DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])


should be modified to

DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus].[Emp ID])

That is changing the exclamation point to a period.

 

[Anne]

You rock! That did it!

UPDATE tblAssigned INNER JOIN tblStatus
ON tblAssigned.[Emp ID] = tblStatus.[Emp ID]
SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=""" & [tblStatus]![Emp ID] & """")
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

If EmpId is a text field then try the above.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I would like to take the most recent Acquired date for each Emp ID from the
Assigned table for a specific certification and place it in the
EAPAnniversary field in the Status table. I have this for the query:

UPDATE tblAssigned INNER JOIN tblStatus ON tblAssigned.[Emp ID] =
tblStatus.[Emp ID] SET tblStatus.EAPAnniversary =
DMax("[Acquired]","tblAssigned","[Emp ID]=" & [tblStatus]![Emp ID])
WHERE (((tblAssigned.[Certification Name])="CRDM Clinical 'Common
Functional' EAP - US"));

But I get a type conversion error. I tried to use DateValue around the DMax
and got the same error. It worked when I did not have criteria in the Dmax,
but of course everyone got the Max date across the domain, not just for
themselves. Both the Acquired and EAPAnniversary fields are Date/Time types.

I'm using Access 2003. Any help appreciated.

 

[Michel Walsh]

Is it possible that tblAssigned.[Emp ID] is of an incompatible data type
with tblStatus.[Emp ID] ?

Is it possible that [Certification Name] is numerical while it is
automatically displayed as a looked-up string when you look at it?


Vanderghast, Access MVP