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, "*"
AS AssetProcess,
Nz(Asset.Function, "*"
AS AssetFunction,
Nz(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]);
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
