Excel内部工作表SQL查询：CAST('double', NULL)执行失败问题

Let’s break down why your cast('double', NULL) AS field1 line is throwing that error, and how to fix it while keeping your requirement of a NULL double-type field for future large numbers.

The Root Cause #

Excel uses the Jet/ACE SQL engine for internal sheet queries, and its syntax for casting differs from standard SQL in two key ways here:

• The CAST function expects the expression first, then the data type (you had them reversed: cast(data_type, expression) instead of cast(expression AS data_type)).
• Data types in Jet/ACE are specified as keywords (like DOUBLE), not string literals wrapped in quotes.

Your original line cast('double', NULL) is invalid syntax for this engine, which triggers the Execute method failure.

Working Solutions #

Here are three reliable ways to create a NULL field with a double data type that works with Excel's query engine:

1. Correct CAST Syntax

Use the proper order and keyword for the data type:

SELECT
    CAST(NULL AS DOUBLE) AS field1,
    -- Your other fields here
FROM [YourSheetName$]

2. Use a Double Constant to Generate NULL

If for some reason the CAST(NULL AS DOUBLE) still causes issues (older Jet versions can be finicky), multiply a double-type zero by NULL. This preserves the double type while returning NULL:

SELECT
    0.0 * NULL AS field1, -- 0.0 is a double, so the result is a NULL double
    -- Your other fields here
FROM [YourSheetName$]

3. Use the CDbl() Function

Jet/ACE supports VBA-style conversion functions. CDbl(NULL) explicitly returns a NULL value with a double data type:

SELECT
    CDbl(NULL) AS field1,
    -- Your other fields here
FROM [YourSheetName$]

All three options will let you run your query without errors, maintain a NULL value for field1, and ensure the field is typed as double for when you later insert large numeric values.

内容的提问来源于stack exchange，提问作者excelguy