Date expression/Combine Columns/Null Values 1

[summer01]

I am fairly new at this and have reached a bump in the road. Below is the SQL for a query I have made. What I would like to do is update one date field (complainceDt) when the service date is greater. I have no clue how to do this and would appreciate it if someone could give me some advice. As a 2nd option, I thought I could create a new field to show the times when the service date is greater than the complaince date (yes), and manually do some updating. It is working find except in those cases where there is no entry for the compliance date. I tried the iff Null, and although it works, I get a #error. If you could assist me with my first option, it would be wonderful. Otherwise if you could point out what is wrong with my expression, that would also help. Thanks.

SELECT AutocamComplianceSort.AutoNum, AutocamComplianceSort.MemAge, AutocamComplianceSort.CONTRACT_NUM, AutocamComplianceSort.MemRelCde, AutocamComplianceSort.CatID, AutocamComplianceSort.Compliant, AutocamComplianceSort.ComplianceDt, Last(ClaimsDetail.SERVICE_DATE) AS LastOfSERVICE_DATE, ([LastOfService_Date])>IIf(IsNull([ComplianceDt]),"yes") AS New
FROM AutocamComplianceSort LEFT JOIN ClaimsDetail ON (AutocamComplianceSort.MemRelCde = ClaimsDetail.RELATIONSHIP_CD) AND (AutocamComplianceSort.CONTRACT_NUM = ClaimsDetail.CONTRACT_NUM)
GROUP BY AutocamComplianceSort.AutoNum, AutocamComplianceSort.MemAge, AutocamComplianceSort.CONTRACT_NUM, AutocamComplianceSort.MemRelCde, AutocamComplianceSort.CatID, AutocamComplianceSort.Compliant, AutocamComplianceSort.ComplianceDt, ([LastOfService_Date])>IIf(IsNull([ComplianceDt]),"yes")
HAVING (((AutocamComplianceSort.CatID)="c"));

 

[genomon]

([LastOfService_Date])>IIf(IsNull([ComplianceDt]),"yes") AS New

Bad syntax. There has to be a value for [ComplianceDt] when it's not null (it's "yes" when it is null). Is [ComplianceDt] a string or a date? You are trying to SELECT something greater than something? Not so, grasshopper. Especially if you are trying to force a type conversion from "yes" to a date, by comparing it to another date.
What you need to do is likely an UPDATE statement, and not a SELECT. There are tons of posts and FAQs in various fora here that you should investigate via the search functionality; also take time to read some of the tutorials on SQL suggested in the FAQs. Good Luck!

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[summer01]

Sorry for the late response back but been out of town and just got back. Thanks for your advise, and I did again check several more posts and I am all set now. sometimes I check so many posts or references, that I confuse myself more.

Thanks again.