473,847 Members | 1,472 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(BDAC mpltDt) AND IS NOT NULL
(RenArch.Cert1) , RenArch.CDDt),
VSel.Cert2ExpDt = IIf(IsNull(BOMS ADtIssd) AND IS NOT NULL
(RenArch.Cert2) , RenArch.CDDt),
VSel.Cert3ExpDt = IIf(IsNull(BDPM ADtIssd) AND IS NOT NULL
(RenArch.Cert3) , RenArch.CDDt),
VSel.Cert4ExpDt = IIf(IsNull(BOAC mpltDt) AND IS NOT NULL
(RenArch.Cert4) , RenArch.CDDt),
VSel.BDACmpltDt = IIf(IsNull(BDAC mpltDt), RenArch.Cert1DO C),
VSel.BOACmpltDt = IIf(IsNull(BOAC mpltDt), RenArch.Cert4DO C),
VSel.BDPMADtIss d = IIf(IsNull(BDPM ADtIssd), RenArch.Cert3DO C),
VSel.BOMSADtIss d = IIf(IsNull(BOMS ADtIssd), RenArch.Cert2DO C)
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

Dec 2 '05 #1
2 4442

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

<IL***@NETZERO. NET> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
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(BDAC mpltDt) AND IS NOT NULL
(RenArch.Cert1) , RenArch.CDDt),
VSel.Cert2ExpDt = IIf(IsNull(BOMS ADtIssd) AND IS NOT NULL
(RenArch.Cert2) , RenArch.CDDt),
VSel.Cert3ExpDt = IIf(IsNull(BDPM ADtIssd) AND IS NOT NULL
(RenArch.Cert3) , RenArch.CDDt),
VSel.Cert4ExpDt = IIf(IsNull(BOAC mpltDt) AND IS NOT NULL
(RenArch.Cert4) , RenArch.CDDt),
VSel.BDACmpltDt = IIf(IsNull(BDAC mpltDt), RenArch.Cert1DO C),
VSel.BOACmpltDt = IIf(IsNull(BOAC mpltDt), RenArch.Cert4DO C),
VSel.BDPMADtIss d = IIf(IsNull(BDPM ADtIssd), RenArch.Cert3DO C),
VSel.BOMSADtIss d = IIf(IsNull(BOMS ADtIssd), RenArch.Cert2DO C)
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

Dec 3 '05 #2
Hello Terry, I did what you suggested and it worked out perfectly.

Thanks!

Dec 6 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
16886
by: polytimi8 | last post by:
Hello, I would like to know if it is possible to create a form in Access2000, which would function like a calendar for 8 operating rooms in hospital, showing which hours are those closed for a specific date. For this intersection I would like to be appeared the name of the doctor, the name of the patient and the kind of operation. Furthemore I would like this intersection to be marked in the calendar with a colour, showing that this...
1
8060
by: annie | last post by:
Hi all, I have recently ported my Access 2000 app to SQL Server, keeping the Access client as the front end using linked tables. I am also using triggers on my SQL tables to trap orphan records and validate added data. My question is..
6
10234
by: Access Newbie | last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to append all the data from a local table to a remote table. I'm using the SQL query editor in MS Access to create the query (I don't want to do this in VB code). I've set up the ODBC connection string in the query properties, which has been tested and works: ODBC;DSN=dev001 student99;Description=dev001...
3
3098
by: Hitesh | last post by:
Hi, I am getting the response from another Website by using the HttpHandler in my current site. I am getting the page but all the images on that page are not appearing only placeholder are displayed. Can anybody know this issue and help me to resolve this. In past i received the response saying that i should download the image first and then parse the actual response and modify the src attribute of the
4
41119
by: Tim Sapp | last post by:
Folks, I am working on a .Net web site that connects to SQL Server 2000 on another box. The DBA has given me a Domain user account with rights to the database and table. I have confirmed with Query Analyzer that the user ID and password are good and can access the correct tables. However, I am unable to get a connection to the database. I either get a Login Failed for (NULL) user. or Not a Trusted Connection, or Login failed for...
2
2341
by: me321701 | last post by:
I have an Access 2003 MDE with a SQL Server 2000 back-end using ODBC linked tables. The MDE is shared among approximately 20 users. This is the environment; I don't have the ability to change it at this time. New reporting requirements really need to use an UDF to supply the result set. Which I believe requires a pass-through query. After changes are made to the development MDB by the development staff
0
3397
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server 2005 and the likes of it. So This one works with SQL2000 What do you think?
8
2812
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table, I have a where clause which states : where PermitID like @WorkType order by WorkStart DESC
2
3222
by: gumby | last post by:
I would like to call this stored procedure, but I am unable to pass parameters to the @Start and @End. Is thier a way to pass parameters to a pass through query from MS Access? SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS , .fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2) AS FROM dbo.tblPersActionLog INNER JOIN
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7888
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5725
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.