[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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