Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)

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

re: Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)

IIF is an Access function it will fail in SQL Server, you need to use an
in-line case statement instead.

In SQL Server IsNull requires two parameters the first is teh var/field to
test the second is the value to reurn if it is Null, you test for Null using
var Is Null.

So your SQL becomes something like ...


UPDATE Vsel
SET
VSel.Cert1 = CASE WHEN VSel.Cert1 Is Null THEN RenArch.Cert1 ELSE
VSel.Cert1 END,
VSel.Cert2 = CASE WHEN VSel.Cert2 Is Null THEN RenArch.Cert2 ELSE
VSel.Cert2 END,
VSel.Cert3 = CASE WHEN VSel.Cert3 Is Null THEN RenArch.Cert3 ELSE
VSel.END,
VSel.Cert4 = CASE WHEN VSel.Cert4 Is Null THEN RenArch.Cert4 ELSE
VSel.Cert4 END,
VSel.Cert1ExpDt = CASE WHEN BDACmpltDt IS NULL AND RenArch.Cert1 IS NOT
NULL
THEN RenArch.CDDt ELSE VSel.Cert1ExpDt
END,
-- ... and so on ...

FROM VSel INNER JOIN RenArch ON VSel.SSN = RenArch.SSN



--
Terry Kreft



<ILCSP@NETZERO.NET> wrote in message
news:1133548696.406657.118660@g44g2000cwa.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)

Hello Terry, I did what you suggested and it worked out perfectly.

Thanks!