What is the MS Access equivalent of SQL Server ISNULL?

I typically don’t spend too much time in Access…so I was a little surprised when I found out that Access doesn’t support ISNULL() or COALESCE() in a query.

Kind of troublesome since I had already agreed to write some Access reports with some optional parameters in them. A little research proved that there were a few ways of tricking access queries into accepting optional parameters by combining “IIF” and “LIKE” statements in the WHERE clause, but it felt dirty, and I knew there had to be a better way.

In SQL Server when adding an optional parameter to a SQL statement we might have something that looks like this.

SELECT

      g.Title,

      g.Price

FROM

      Games g

WHERE

      g.Title = ISNULL(@p_Title, g.Title)

In the above statement ISNULL will look at the value of @p_Title, and if it’s not null, the queries WHERE statement will evaluate to

WHERE

      g.Title = @p_Title

However if the value of @p_Title is NULL then the where statement would evaluate to

WHERE

      g.Title = g.Title

Since g.title will always be equal to itself, the query will return all game titles….thus making it an optional parameter.

However in Access ISNULL only takes one argument and returns a bool value telling us if the value is NULL or not. However we can get the same SQL Server ISNULL functionality we’re looking for in Access using the NZ() function. NZ() actually works exactly like ISNULL in this scenario….like so:

SELECT [Games].[Title], [Games].[Price]

FROM Games

WHERE [Games].[Title] = NZ(Forms!GameReport.ddTitle, [Games].[Title])

In the example above I’m grabbing my parameter value from a form that I’ve built, but other than that our first and 2nd queries would evaluate the same. Hope this helps.

 

I’ll be speaking at "SharePoint Cincy – 2011"

I'll be speaking at the "SharePoint Cincy - 2011" conference on March 18th. If you're looking to dive into the world of SharePoint Dashboarding but aren't sure where to start, here's your chance!

The event website is here: http://www.sharepointcincy.com

Even though they failed to post my bio under the speakers page (....sigh) you'll still find me mentioned on the Sessions page under the BI track here: http://www.sharepointcincy.com/session-information/13

Or you can just read my session abstract below.

Subject: Implementing Dashboard Solutions Using PerformancePoint & SQL Reporting Services in SharePoint 2010
Presenter: Tavis Lovell – Ascendum, Senior Consultant

Learn how to build Reports, KPIs, Scorecards and Dashboards for deployment to SharePoint 2010 using PerformancePoint Services (PPS) and SQL Server Reporting Services (SSRS). Attendees will gain familiarity with PerformancePoint Dashboard Designer and SQL Servers Business Intelligence Development Studio (BIDS) and how they can be leveraged within an organization to meet strategic, tactical, and operational goals.

Hope to see you there.

 

How to update a SharePoint List using JavaScript(jQuery)

I recently had a situation where I needed to update a SharePoint list value directly from the lists display form. Probably not something most people find themselves needing to do. However it’s not totally uncommon to have a need to update a list record outside of the lists standard edit form. Typically you need to write some CAML and make a jQuery ajax call, however turns out there is a somewhat easier way thanks to the SPServices jQuery library. In my example I was able update my needed row using the following call:

             $().SPServices({

             operation: "UpdateListItems",

             listName: "MyList",

             ID: RowID,

             valuepairs: [["IsDeleted", true]],

             completefunc: function (xData, Status) {alert(Status);}

             });

In this case I was updating the column “IsDeleted” to have a value of true in the list “MyList”. All you have to do is pass in the ID of the row you want to update. Very easy to use, and no CAML required. In this example I simply have my callback throwing an alert to the page on the status of the call once it’s complete. You can download the SPServices library on CodePlex here: http://spservices.codeplex.com/. Simply unzip the downloaded file and add a reference to it in your page. You also need to have the core jQuery file referenced in your page as well, you can find that here: http://jquery.com/. Be sure to check out the documentation of other functions in the SPServices library at http://spservices.codeplex.com/documentation.

Hope this helps!