I know you don't like VB specific functions
I have no problem with the VBA5 scalar functions because they have been
implemented in the Jet 4.0 DLLs ;-) I supposed in the context of SQL we
should call them 'expressions'. I use the word 'implemented' because
some of the Jet functions seem to work differently to their VBA
equivalents e.g. IIF() in Jet appears not to evaluate both true and
false conditions as it does in VBA. And I use the term 'seem to'
because there is no Jet specification to tell the designer's intention.
If you imbed into the SQL definition of a Jet database object (e.g. a
VIEW or Query) a VBA6 function or something specific to a front end
application (Access object model, UDF in a module, etc) then it would
require the Access user interface to work, which would be a little
shorted sighted at least (although this will become increasingly
irrelevant if the Access 2007 format and features have good take up).
It's a shame the Jet team never got around to implementing the VBA6
functions into Jet; Replace$() and StrReverse$() are particularly
missed. The principle seems to have been that only scalar expressions
(as distinct from set functions or 'aggregates') present in VBA can be
implemented in Jet, which is a shame because COALESCE() and NULLIF()
from the SQL standard would have be extremely useful too. I guess with
ACE (Access 2007 engine) the argument is now that the best place to
implement functions is the Access object model, where NZ() has existed
for considerable time.
null-to-zero function might look more "elegant"...
{untested}
WHERE
Nz([City],Chr(0)) =
IIF(arg_City IS NULL, Nz([City],Chr(0)), arg_City);
Using Chr(0) makes porting to another SQL product unnecessarily hard -
why not use a placeholder in single quotes? It was IMO improve
readability too. [That said, IIRC I used CHR(0) myself recently because
the empty string does not render well as '' in a newsgroup post.]
I used '{{NONE}}' because that's what I would have used as the DEFAULT
when I made the column NOT NULL. For me, null city makes no sense; I
see more value in using placeholders for 'not known', 'does not apply'
and 'none' respectively, with double curly brackets to indicate that
the values are placeholders. I reserve NULL as a placeholder for a
value I know will be coming but cannot be known at present e.g. a
star_date and end_date pair in a valid-time state ('history') table
where a null value for end_date indicates the current period (i.e.
there is no end date because it hasn't ended...yet).
I probably shouldn't have brought it up --
it really was nitpicking
You should know I appreciate nitpicking <g>.
Jamie.
--