In an SQL Server View, a problem is that the DateTime field has many Null values which is causing a problem with the parsing of the data in MSAccess.
How to I use CAST (or CONVERT) to handle Null Date values in my SQL Server view?
There is a way to use CAST or CONVERT similar to the nz type function in MSAccess to handle null date values but I can’t remember the syntax.
What is happening now is that I get a data mismatch in my MSAccess function when it hits a Null Date value. Can I somehow use the ISNULL or ISDate function? I believe I need to somehow return “” instead of Null.
COALESCE
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,…n ] )
Arguments
expression
Is an expression of any type.
n
Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible
to the same type.
Return Types
Returns the same value as expression.
Remarks
If all arguments are NULL, COALESCE returns NULL.
COALESCE(expression1,…n) is equivalent to this CASE function:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1 …
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END
Example:
set @dateval = coalesce (InDateVal,'1900-01-01')