Link to home
Start Free TrialLog in
Avatar of P_Strome
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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hi P_Strome,

What happens if you use Val() instead of CLng()?

Regards,

Patrick
Avatar of P_Strome
P_Strome

ASKER

I still get the same error:


[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
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.
What data is in the column, and what is the calculation involved?
Regards,
Rory
Here is the calculation:

Open Amount: (([TIA_ALLOWANCES]![TOTAL_AMOUNT]-[TIA_ALLOWANCES]![CASH_AMOUNT]-[TIA_ALLOWANCES]![ABATEMENT_AMOUNT]))

All the fields are numbers in the access table
SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh, and what kind of numbers are they in Access?
Rory
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
I figured it out.

I put iif(Isnull) statements in the query.

Open Amount: (([TIA_ALLOWANCES]![TOTAL_AMOUNT]-IIf(IsNull([TIA_ALLOWANCES]![CASH_AMOUNT]),0,[TIA_ALLOWANCES]![CASH_AMOUNT])-IIf(IsNull([TIA_ALLOWANCES]![ABATEMENT_AMOUNT]),0,[TIA_ALLOWANCES]![ABATEMENT_AMOUNT])))



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
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