Data type conversion problem when nulls are encountered in ADO query

Question

I have the following ADO query that reads data from an Excel file:

SELECT [Sheet1$].[Project_ID]

FROM [Sheet1$]

WHERE ((([Sheet1$].[Active_Indicator])=1));

Unfortunately, this query causes a "Run-time error 6: Overflow" because the value of Active_Indicator can be either 0, 1, or null, and the ACE engine appears to have a problem determining the correct data type (I assume due to the null values).

I've tried adding the IIf and IsNull functions to the WHERE clause, but I still get the Overflow error message.

Can someone kindly tell me how to modify my query to avoid the Overflow problem?  Thanks in advance for any assistance.

*** UPDATE ***

After doing some more research, I discovered that if I modify my query as follows:

SELECT [Sheet1$].[Project_ID]

FROM [Sheet1$]

GROUP BY [Sheet1$].[Project_ID], [Sheet1$].[Active_Indicator]

HAVING (Not([Sheet1$].[Active_Indicator]) Is Null);

it works, and I don't get the Overflow error message.  However, if I modify the HAVING clause further in order to filter out nulls, and include only those records where Active_Indicator = 1 (which is what I really want to achieve):

HAVING (Not([Sheet1$].[Active_Indicator]) Is Null) And ([Sheet1$].[Active_Indicator]=1)

I get the Overflow error message again.

Answer 1

Thank you for your suggestion.  Yes, I could do that, but the primary reason I'm using ADO to access and read the Excel file is because it contains over 800,000 rows, and it resides on a network share.  So rather than downloading the entire file, my query reads only a subset of the file, thereby minimizing network traffic.

Answer 2

As a work-around, you could get hold of all of the data and then parse it to delete the records that you do not require.