Hello, I'm in the process of changing our 'normal' Access 2000 update
queries to Update Pass Through Queries. We have a SQL server 2000
database and we're using an Access 2000 database as our front end.
In the criteria of one of our update query fields, we use both the
isnull(field1) and Not IsNull(field2) then Field3, otherwise, set the
updated field as null. Here's my converted Pass Through Query :
UPDATE Vsel SET
VSel.Cert1 = IIf(IsNull(VSel.Cert1), RenArch.Cert1),
VSel.Cert2 = IIf(IsNull(VSel.Cert2), RenArch.Cert2),
VSel.Cert3 = IIf(IsNull(VSel.Cert3), RenArch.Cert3),
VSel.Cert4 = IIf(IsNull(VSel.Cert4), RenArch.Cert4),
VSel.Cert1ExpDt = IIf(IsNull(BDACmpltDt) AND IS NOT NULL
(RenArch.Cert1), RenArch.CDDt),
VSel.Cert2ExpDt = IIf(IsNull(BOMSADtIssd) AND IS NOT NULL
(RenArch.Cert2), RenArch.CDDt),
VSel.Cert3ExpDt = IIf(IsNull(BDPMADtIssd) AND IS NOT NULL
(RenArch.Cert3), RenArch.CDDt),
VSel.Cert4ExpDt = IIf(IsNull(BOACmpltDt) AND IS NOT NULL
(RenArch.Cert4), RenArch.CDDt),
VSel.BDACmpltDt = IIf(IsNull(BDACmpltDt), RenArch.Cert1DOC),
VSel.BOACmpltDt = IIf(IsNull(BOACmpltDt), RenArch.Cert4DOC),
VSel.BDPMADtIssd = IIf(IsNull(BDPMADtIssd), RenArch.Cert3DOC),
VSel.BOMSADtIssd = IIf(IsNull(BOMSADtIssd), RenArch.Cert2DOC)
FROM VSel INNER JOIN RenArch ON VSel.SSN = RenArch.SSN
This is the original criteria in the access update query for line #6
(when I start to use both is null and is not null)
IIf(IsNull([BDACmpltDt]) And Not
IsNull([RenArch].[Cert1]),[RenArch].[CDDt],Null)
When I run this pass through query, I get an error telling me that the
IS Null function requires 2 arguments. I know the lines for
Can anyone help me with this.
I would certainly appreciate it.
JR