• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

isnull function requires 2 argument - problem with sql statements moving from access to sql server 2005

I'm trying to move my back end database from access to sql server 2005 and am really just learning sql server. I am getting the following error for the sql statement below. I imagine it has something to do with how sql server deals with null values.
As I am still in the testing phase and still have the live version in access if possible I would like to find a solution that works in both access and sql server.
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The isnull function requires 2 argument(s).  
 
SELECT tblLanguage.[LanguageID], IsNull([Priority]) AS NotPriority, tblLanguage.Priority, tblLanguage.English AS SLang FROM tblLanguage ORDER BY IsNull([Priority]) DESC , tblLanguage.Priority, tblLanguage.English;  
VENDORERRORCODE   174 
DATASOURCE   araxisql

Open in new window

0
Shawn
Asked:
Shawn
4 Solutions
 
AanvikCommented:

SELECT tblLanguage.[LanguageID], IsNull([Priority]) AS NotPriority, tblLanguage.Priority, tblLanguage.English AS SLang FROM tblLanguage ORDER BY IsNull([Priority],0) DESC , tblLanguage.Priority, tblLanguage.English;  
 
IsNull([Priority],0) This is what I changed...Priority is null you will get 0 as return.

Open in new window

0
 
ShawnAuthor Commented:
this makes the error go away but it doesn't work in the access query bulder. I reall yam looking for something that works in both access and sql server
0
 
AanvikCommented:
Access ISNull will return true or False based on the value .... SQL Server can return whatever you want  if the value is is null..

So I guess if you are ok. Isnull (Value,0) will return 0 if Value is null in SQL Server and Isnull(Value) will return true if value is null in Access.

 
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ShawnAuthor Commented:
is there not a way to have the same arguement for both? Isnull (Value,0)  doesn't work in Access.
otherwise I'm going to have to write 2 sql statements for every query. :(
0
 
ShawnAuthor Commented:
I tried this workaround and it doesn't work in SQL server either but might help find a workaround. in access is gives the same results.

i need to find a function that will work in both environments

SELECT tblLanguage.LanguageID, IIf([Priority]>=1,1,0) AS [not], tblLanguage.Priority, tblLanguage.English AS SLang FROM tblLanguage ORDER BY IIf([Priority]>=1,1,0) DESC , tblLanguage.Priority, tblLanguage.English; 

Open in new window

0
 
azadisaryevCommented:
ms access's and sql server's fucntions to deal with null values are different (no surprise there, eh?).

in order to be able to use the same function in sql server as you do in ms access, you will have to create such a function in sql server yourself.

a good solution would be to use NZ() ms access function in your queries - it uses same syntax as sql server's isnull() function - and create a corresponding NZ() function in your sql server.

here's a link to sample code for creating NZ() function in sql server:
http://www.databasejournal.com/scripts/article.php/3499161/NZ-function-in-SQL-Server.htm

hth

Azadi
0
 
Anthony PerkinsCommented:
>>I reall yam looking for something that works in both access and sql server<<
To put it mildly it ain't going to happen.  MS Access uses a different SQL dialect then MS SQL Server.  If this is the first discrepancy you have found consider yourself lucky or you are just starting.
0
 
_agx_Commented:
> To put it mildly it ain't going to happen.

  +1.  

   While there are many similarities in syntax, they are two totally different beasts.  Trying to get 100%
   compatibility is near impossible for anything beyond very basic statements.

   Besides, MS SQL is much more powerful than Access.  Trying to code to Access's standards means
   you may be creating less efficient queries, which kind of defeats the purpose of using a more powerful  
   database in the first place.  
0
 
ShawnAuthor Commented:
OK, I'm convinced they are 2 different beasts and we should look at them differently. It would have been 'nice' in this transition phase to have a common solution...but in the end I will have much more control with sql server.

I'm sure I'll be posting more questions as I make my way through the access >> sql server transition.

thanks for all the iinput.
<cfif application.dbtype is "sql server">
<cfquery name="getSLang" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
SELECT tblLanguage.LanguageID, IsNull([Priority],0) AS [not], tblLanguage.Priority, tblLanguage.English AS SLang
FROM tblLanguage
ORDER BY IsNull([Priority],0) DESC , tblLanguage.Priority, tblLanguage.English;
</cfquery>
 
<cfelse>
<cfquery name="getSLang" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
SELECT tblLanguage.LanguageID, Nz([Priority],0) AS [not], tblLanguage.Priority, tblLanguage.English AS SLang
FROM tblLanguage
ORDER BY Nz([Priority],0) DESC , tblLanguage.Priority, tblLanguage.English;
</cfquery>
</cfif>

Open in new window

0
 
ShawnAuthor Commented:
hope you're all ok with point distribution. Azadi came closer to a workaround but acperkins: and agx_: knocked some sense into me ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now