Re: Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)
- From: "Terry Kreft" <terry.kreft@xxxxxxxxx>
- Date: Fri, 2 Dec 2005 20:45:33 -0000
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@xxxxxxxxxxx> wrote in message
news:1133548696.406657.118660@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
>
.
- Prev by Date: Re: TransferText and First Record in Text file
- Next by Date: Re: are ado questions allowed here?
- Previous by thread: TransferText and First Record in Text file
- Next by thread: Blank page after report header
- Index(es):
Relevant Pages
|
Loading
