-
1. Re: Custom SQL. Data type mismatch in criteria expression.
Hari Ankem Apr 12, 2018 11:10 PM (in response to Alexandre PAREJA-GARCIA)Since there are nulls for the column values, you should type cast them so that a column has the same data type across the unions.
See if this works:
Select
Cstr( [Account]) AS Account
,Cstr([Contract Number]) AS Contract_Number
,Cdbl(Null) as Estimated_Amount
From
[VIEW_ACCOUNT_BOOKINGS_REVENUE].[VIEW_ACCOUNT_BOOKINGS_REVENUE]
Union
Select
Cstr( [Account]) AS Account
,Cstr(Null) AS Contract_Number
,Cdbl([Estimated Amount (SUM)]) as Estimated_Amount
From
[VIEW_ACCOUNT_CONTRACT].[VIEW_ACCOUNT_CONTRACT]
Union
Select
Cstr(Null) AS Account
,Cdbl(Null) as Estimated_Amount
From
[VIEW_ACCOUNT_HOURS_COST].[VIEW_ACCOUNT_HOURS_COST]
-
2. Re: Custom SQL. Data type mismatch in criteria expression.
Alexandre PAREJA-GARCIA Apr 12, 2018 11:41 PM (in response to Hari Ankem)Hi Hari,
Thanks for your reply. Unfortunately it is not working as I get the error: "Microsoft JET database error 0x80040E14: Invalid use of Null"
I tried to remove the cstr from the null and then get the previous error message: Microsoft JET database error 0x80040E07: Data type mismatch in criteria expression.
-
3. Re: Custom SQL. Data type mismatch in criteria expression.
Alexandre PAREJA-GARCIA Apr 19, 2018 7:02 PM (in response to Alexandre PAREJA-GARCIA)Hi,
Anyone have been running through the same issue?
Regards,
Alex
-
4. Re: Custom SQL. Data type mismatch in criteria expression.
Hari Ankem Apr 19, 2018 7:29 PM (in response to Alexandre PAREJA-GARCIA)Am sorry, I missed a column in the third union. Also, made a change to have zeroes instead of null for the amount. Can you please try this?
Select
Cstr( [Account]) AS Account
,Cstr([Contract Number]) AS Contract_Number
,0 as Estimated_Amount
From
[VIEW_ACCOUNT_BOOKINGS_REVENUE].[VIEW_ACCOUNT_BOOKINGS_REVENUE]
Union
Select
Cstr( [Account]) AS Account
,Cstr(Null) AS Contract_Number
,Nz([Estimated Amount (SUM)],0) as Estimated_Amount
From
[VIEW_ACCOUNT_CONTRACT].[VIEW_ACCOUNT_CONTRACT]
Union
Select
Cstr(Null) AS Account
,Cstr(Null) AS Contract_Number
,0 as Estimated_Amount
From
[VIEW_ACCOUNT_HOURS_COST].[VIEW_ACCOUNT_HOURS_COST]