Help with isnull in SQL statement

[tonyblazek]

Hello all, a guy who knows nothing about SQL here. I built a query in Access and I would like to modify it so if one or more of the values is null it is replaced with a wild card. Following is the SQL generated by my Access query,

SELECT Asset.[Asset ID], Asset.Name, Asset.Description, Asset.Location, Asset.Process, Asset.Function, Asset.Type
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]));

I got out my SQL book and added the isnull(see below) but it doesn't work?

SELECT Asset.[Asset ID], Asset.Name, Asset.Description, Asset.Location, Asset.Process, Asset.Function, Asset.Type
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]) AND (( isnull(Asset.Process, "*&quot AND ((isnull(Asset.Function, "*&quot AND ((isnull(Asset.Type, "*"

Where did I go wrong? Thanks, Tony

 

[RickSpr]

I'm not familiar with this isnull() function, but I presume you got it from the SQL manual for another product (or maybe it's ANSI standard?). In any case, Jet SQL doesn't implement this function this way.

Instead, it's possible to use the VBA IsNull() function, which is similar, within your SQL statements. To convert this to the VBA IsNull, just remove the second arguments, the "*" part.

More efficient, though, is to use the Jet IS NULL predicate. An example is: Asset.Process IS NULL. Using the Jet predicate will be much more efficient in complex queries.

When you're having trouble with SQL in Access, it's best to look up the statement syntax in Access Help, rather than standard SQL texts. Every DBMS has its idiosyncracies in the SQL that it implements. You can still use the standard texts to get ideas, but check the final syntax against the Help file. Rick Sprague

 

[tonyblazek]

THANKS, I get it. You say just remove "*", then how do I tell it to replace the null with a wild card? TB

 

[THoey]

Hey Tony, try this:

SELECT Asset.[Asset ID], 
       Asset.Name, 
       Asset.Description, 
       Asset.Location, 
       IIF(Asset.Process IS NULL, "*", Asset.Process), 
       IIF(Asset.Function IS NULL, "*", Asset.Function), 
       IIF(Asset.Type IS NULL, "*", Asset.Type)
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]);

I didn't test it, but it should work. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[tonyblazek]

Thanks Terry. I made the changes you suggested but didn't realize and additional functionality? The query still requires all 3 values to return a record? I've got the controsl default value set to = null and your SQL seems correct (Access didn't barf on the save)??? Any more ideas? Thanks, Tony

 

[THoey]

Hey Rick, what did I miss there? The only thing I can think of is that for the three fields with the IIF, I didn't give them a name (ie, AS FIELD1). So maybe it is working, but the form doesn't know where to display the values.

SELECT Asset.[Asset ID], 
       Asset.Name, 
       Asset.Description, 
       Asset.Location, 
       IIF(Asset.Process IS NULL, "*", Asset.Process) AS AssetProcess, 
       IIF(Asset.Function IS NULL, "*", Asset.Function) AS AssetFunction, 
       IIF(Asset.Type IS NULL, "*", Asset.Type) AS AssetType
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]);

Tony, try saving this query and then building a form through the wizard based on it. Or you could change the data source for those three fields to the names I put after the AS clause (or rename them to what you want)
Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[tonyblazek]

Thanks again Terry, it didn't make any difference, I made a new form with the same results. This is driving me crazy and giving me more respect for the db guys. Got any more ideas? Thanks, TB

 

[RickSpr]

Well, let's see...There's an unbalanced left parenthesis at the start of the WHERE clause. Tony said Access didn't barf, so I guess he must have fixed that, either by deleting one of the left parens or adding a right paren at the end of the statement--either would work.

I'm not sure, but I suspect the problem is the use of IS NULL inside the IIf() function. What was meant was the SQL 'IS NULL' predicate, which returns a truth value, but I don't think it will work that way in this case. Here's why:

When Access starts up the Jet engine, it hooks up Jet with the VBA compiler. This allows Jet to call VBA whenever Jet finds something it doesn't understand in the SQL statement. Because of this, you can use VBA functions, and references to form and report controls, within Jet SQL statements. This hookup is called the "expression service".

In the present case, IIf() is not a Jet function, so Jet passes it to VBA to see if he can figure it out. But what Jet passes is the whole function call, that is, the string "IIf(Asset.Process IS NULL, "*", Asset.Process)".

VBA treats this as an expression to be evaluated the same way it would in compiled code. But you see the 'IS NULL' in there? You meant that to be an SQL predicate, but it also happens to be a perfectly good VBA operation (it tests whether Asset.Process returns a Variant value of the Null subtype). You might more easily recognize it with initial capitals: Is Null. In VBA we would typically use it to test passed parameters, as in [red]If parm1 Is Null Then...[/red].

I hope you can sense the problem now, because It's subtle and hard to explain. There's a difference between a null value in a table column and a null variable. A null table column, if you read it from a recordset, normally gets translated into a null variant in your VBA code, but they're not the same thing. After all, you can test whether a variant Is Null even if it never has anything at all to do with a table column. So I think what's happening is that VBA is interpreting the IS NULL as a VBA function that has nothing to do with the table column's value.

Even if VBA didn't have its own meaning of 'Is Null', this statement wouldn't have worked. The expression service only works in one direction--Jet to VBA, not VBA to Jet. Jet passed "IIf(Asset.Process IS NULL, "*", Asset.Process)" to VBA. If VBA couldn't figure out what the "IS NULL" part meant, it doesn't have the option to call on Jet for help; so VBA would have just returned a "Syntax Error" result to Jet, and Jet would have returned an error code to Access, who would have displayed a message box to the user (or raised a runtime error in the module that was executing).

In short, then, I think it fails because you included an SQL predicate within a VBA function. That's not supported. You didn't get a syntax error because it happens to have been valid VBA syntax as well, but that was just a coincidence.

Now for a solution. Instead of IS NULL, you could have used the VBA IsNull() function, like this:
SELECT Asset.[Asset ID],
Asset.Name,
Asset.Description,
Asset.Location,
IIF(IsNull(Asset.Process), "*", Asset.Process) AS AssetProcess,
IIF(IsNull(Asset.Function), "*", Asset.Function) AS AssetFunction,
IIF(IsNull(Asset.Type), "*", Asset.Type) AS AssetType
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]);

(Note to Terry: Yes, I do think you should provide an AS clause; otherwise what name would it have?)

However, there's a shortcut in this case, where the IIf is being used to translate nulls into something non-null. VBA provides an Nz() function for this exact purpose. So you can code this instead:
SELECT Asset.[Asset ID],
Asset.Name,
Asset.Description,
Asset.Location,
Nz(Asset.Process, "*&quot AS AssetProcess,
Nz(Asset.Function, "*&quot AS AssetFunction,
Nz(Asset.Type, "*&quot AS AssetType
FROM Asset
WHERE (((Asset.Process)=[Forms]![Asset_PFT]![PFT_Process]) AND ((Asset.Function)=[Forms]![Asset_PFT]![PFT_Function]) AND ((Asset.Type)=[Forms]![Asset_PFT]![PFT_Type]);

Try that out, Tony.

-----------------------------------------
A final note for people who wonder how I know this stuff:
The reality is, I don't, exactly, at least not all of it. Some of it is educated guesswork from 25 years in computers, plus some knowledge of how compilers and interpreters work internally. But however educated, it's still partly guesswork, so if something happens that contradicts an explanation of mine, believe your machine, not me. And if you wouldn't mind, let me know, so I can repair the flaw in my own understanding. Rick Sprague

 

[tonyblazek]

Hello Rick, I tried both and I still need all three values to return a record? Got any more tricks up your sleeve? Tony

 

[RickSpr]

Now that I've gone back and read your initial post again, I see your use of "*" was an attempt to set up a wildcard for omitted criteria. I had lost track of that, and was just working on generating "*" as a substitute for Null data.

It isn't working because "*" as a wildcard only works with the LIKE predicate. Since you're not using LIKE, it's being taken as a literal string and the column data is being compared to an asterisk character. Presumably, that is always False, so if you leave any parameters null, you won't find anything.

You could either include LIKE in your SQL criteria, or you could change the criteria to explicitly test for Null. I'll show you both, but first let me explain the slight difference between them. If you use LIKE, any "*" or "?" characters your users key within parameters will be interpreted as wildcards. If you explicitly test for Null, any "*" or "?" characters will be taken literally. Which one corresponds to your needs is up to you.

Using the LIKE operator:
SELECT Asset.[Asset ID],
Asset.Name,
Asset.Description,
Asset.Location,
Asset.Process,
Asset.Function,
Asset.Type
FROM Asset
WHERE (Asset.Process LIKE Nz([Forms]![Asset_PFT]![PFT_Process], "*&quot)
AND (Asset.Function LIKE Nz([Forms]![Asset_PFT]![PFT_Function], "*&quot)
AND (Asset.Type LIKE Nz([Forms]![Asset_PFT]![PFT_Type], "*&quot);

Using an explicit test for Null:
SELECT Asset.[Asset ID],
Asset.Name,
Asset.Description,
Asset.Location,
Asset.Process,
Asset.Function,
Asset.Type
FROM Asset
WHERE ((Asset.Process=[Forms]![Asset_PFT]![PFT_Process]) OR IsNull([Forms]![Asset_PFT]![PFT_Process]))
AND ((Asset.Function=[Forms]![Asset_PFT]![PFT_Function]) OR IsNull([Forms]![Asset_PFT]![PFT_Function]))
AND ((Asset.Type=[Forms]![Asset_PFT]![PFT_Type]) OR IsNull([Forms]![Asset_PFT]![PFT_Type]));

There's one more variation that most often pays a double bonus: Always consider whatever the user enters as a prefix, and append a wildcard at the end. With this, your users can key partial values, and your SQL code can be the simplest yet. The only downside is that users can't limit the data to matching values which happen to be prefixes of other values. In other words, they can't limit their data to rows having "Smith" if there are also "Smithers" and "Smithson" present. But if that's not a problem, you can do this:
SELECT Asset.[Asset ID],
Asset.Name,
Asset.Description,
Asset.Location,
Asset.Process,
Asset.Function,
Asset.Type
FROM Asset
WHERE (Asset.Process LIKE [Forms]![Asset_PFT]![PFT_Process] & "*&quot
AND (Asset.Function LIKE [Forms]![Asset_PFT]![PFT_Function] & "*&quot
AND (Asset.Type LIKE [Forms]![Asset_PFT]![PFT_Type] & "*"
Rick Sprague

 

[tonyblazek]

Hello Rick, it works great!! Thank you for your time and effort, I can't tell you how big of help you've been. Not only have your solved my problem you've taught me a bunch along the way! Your explanations are wonderful. Again, thanks!! Tony