Use SELECT query as criteria in UPDATE Query? 1

[ponderdj]

The SQL looks like this:

UPDATE tblUpdate INNER JOIN tblCOPS ON tblUpdate.CSAText = tblCOPS.CSA SET tblUpdate.Customer = "Air Force"
WHERE (((tblUpdate.Customer) Is Null) AND ((tblCOPS.PDC) Like "J*" Or (tblCOPS.PDC) Like "1*" Or (tblCOPS.PDC) Like "A*" Or (tblCOPS.PDC) Like "D*" Or (tblCOPS.PDC) Like "L*" Or (tblCOPS.PDC) Like "KA*" Or (tblCOPS.PDC) Like "W1*" Or (tblCOPS.PDC) Like "W9*" Or (tblCOPS.PDC) Like "WA*" Or (tblCOPS.PDC) Like "WL*" Or (tblCOPS.PDC) Like "WCENPM" Or (tblCOPS.PDC) Like "WCESPM") AND ((tblCOPS.COPSOutputDate)=[qryMaxOutputDate_COPS].[MaxDate]));

The problem is with the end of the Where clause. It prompts me for [qryMaxOutputDate_COPS].[MaxDate] as if it doesn't know where to look for it.

Thanks

 

[dhookom]

Substitute:
AND tblCOPS.COPSOutputDate)=DLookup("MaxDate","qryMaxOutputDate_COPS") );


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ponderdj]

Just what I needed, thank you!