P_Strome
asked on
invalid character value
In using MS Query, receving "invalid character value" error on a calculated field from MS Access. We tried to convert it by using the CLng fuction, but that did not resolve the issue. Any thoughts?
ASKER
I still get the same error:
[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification on column number 10(val([FIELD])
[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification on column number 10(val([FIELD])
OK, so it appears to be text that cannot be implicitly converted to a number.
Try CStr().
Patrick
Try CStr().
Patrick
ASKER
I received the same error.
When we open the query in MS Query first, the column is properly populated. It fails once we attempt to open it in Excel.
When we open the query in MS Query first, the column is properly populated. It fails once we attempt to open it in Excel.
What data is in the column, and what is the calculation involved?
Regards,
Rory
Regards,
Rory
ASKER
Here is the calculation:
Open Amount: (([TIA_ALLOWANCES]![TOTAL_ AMOUNT]-[T IA_ALLOWAN CES]![CASH _AMOUNT]-[ TIA_ALLOWA NCES]![ABA TEMENT_AMO UNT]))
All the fields are numbers in the access table
Open Amount: (([TIA_ALLOWANCES]![TOTAL_
All the fields are numbers in the access table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh, and what kind of numbers are they in Access?
Rory
Rory
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There are nulls in the data.
When I use this equation in access I get an error saying that commas are used in the equation.
when I use this equation in MS Query I get a syntax error.
the query does export to excel from MS Query if I take this statement out.
When I use this equation in access I get an error saying that commas are used in the equation.
when I use this equation in MS Query I get a syntax error.
the query does export to excel from MS Query if I take this statement out.
ASKER
I figured it out.
I put iif(Isnull) statements in the query.
Open Amount: (([TIA_ALLOWANCES]![TOTAL_ AMOUNT]-II f(IsNull([ TIA_ALLOWA NCES]![CAS H_AMOUNT]) ,0,[TIA_AL LOWANCES]! [CASH_AMOU NT])-IIf(I sNull([TIA _ALLOWANCE S]![ABATEM ENT_AMOUNT ]),0,[TIA_ ALLOWANCES ]![ABATEME NT_AMOUNT] )))
The null question pointed me in the right direction.
Thanks.
I put iif(Isnull) statements in the query.
Open Amount: (([TIA_ALLOWANCES]![TOTAL_
The null question pointed me in the right direction.
Thanks.
Glad you figured it out, but you should have given Rory at least a share;
after all, he is the one who brought up the issue of nulls to begin with...
Patrick
after all, he is the one who brought up the issue of nulls to begin with...
Patrick
ASKER
Oh, how do I do that?
I've sent a note to Brad, the Page Editor, asking him to reopen the question.
Once Brad does that, read this link to see how to split the points:
https://www.experts-exchange.com/help.jsp#hi69
Regards,
Patrick
Once Brad does that, read this link to see how to split the points:
https://www.experts-exchange.com/help.jsp#hi69
Regards,
Patrick
What happens if you use Val() instead of CLng()?
Regards,
Patrick