Invalid Character Error when i "refresh data"

[Chris]

I go to refresh data in excel where I have ODBC linking in an Access query
and I get an error that says

[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast
specification on column 5

What does this mean and how can I correct it?

 

[Jan Karel Pieterse]

Hi Chris,

Invalid character value for cast
specification on column 5

What does this mean and how can I correct it?

It means there is a column name (or alias) Excel/msQuery does not understand.
Could you post the SQL of the query here?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[CJoyce]

Jan, I am getting exactly the same error on column 4 for the same type of
query. I didn't see anything further on this so, I'm posting my sql in hopes
that you will be able to tell me what is wrong. I have been through this and
through this and have been unable to determine what is different about this
one from the ones that are working. The only thing that I have noticed is
that the SQL view in MS Query does return the data and there are negative
zeros in the returned data. I did change the column name both in Access and
through SQL in MS Query. Here is my SQL:
SELECT `Interest_Other Income Link`.masked_glnum, `Interest_Other Income
Link`.RTrim, `Interest_Other Income Link`.gldes, `Interest_Other Income
Link`.`PYActuals`, `Interest_Other Income Link`.JanCY, `Interest_Other Income
Link`.FebCY, `Interest_Other Income Link`.MarCY, `Interest_Other Income
Link`.AprCY, `Interest_Other Income Link`.MayCY, `Interest_Other Income
Link`.JunCY, `Interest_Other Income Link`.JulCY, `Interest_Other Income
Link`.AugCY, `Interest_Other Income Link`.SepCY, `Interest_Other Income
Link`.OctCY, `Interest_Other Income Link`.NovCY, `Interest_Other Income
Link`.DecCY, `Interest_Other Income Link`.`CY Budget`
FROM `K:\CR-data\Accounting\2008\Budget\Budget 08_Attorney &
Professional`.`Interest_Other Income Link` `Interest_Other Income Link`

 

[Jan Karel Pieterse]

Hi CJoyce,

I am getting exactly the same error on column 4 for the same type of
query. I didn't see anything further on this so, I'm posting my sql in hopes
that you will be able to tell me what is wrong.

Well, I see no obvious problem with the query and the fact that msquery does
show results proves that. One thing to try though is to replace all single
quotes with the apropriate square brackets:

SELECT [Interest_Other Income Link].masked_glnum, [Interest_Other Income
Link].RTrim, [Interest_Other Income Link].gldes, [Interest_Other Income
Link].[PYActuals], [Interest_Other Income Link].JanCY, [Interest_Other Income
Link].FebCY, [Interest_Other Income Link].MarCY, [Interest_Other Income
Link].AprCY, [Interest_Other Income Link].MayCY, [Interest_Other Income
Link].JunCY, [Interest_Other Income Link].JulCY, [Interest_Other Income
Link].AugCY, [Interest_Other Income Link].SepCY, [Interest_Other Income
Link].OctCY, [Interest_Other Income Link].NovCY, [Interest_Other Income
Link].DecCY, [Interest_Other Income Link].[CY Budget]
FROM [Interest_Other Income Link] [Interest_Other Income Link]

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[CJoyce]

Jan,

Thank you for looking at that. I tried your suggestion and I wish I could
tell you that that worked but it didn't. I also tried to just redo the query
completely and it still doesn't like something. It is so weird because I
have this same query principle running on at least a dozen other queries
within this workbook. It has chosen only three of them to decide not to
work. I'm at my wits end with this, especially since I can't even just
rebring the whole thing in in a new query. The one common factor I see in
these is that there are negative number results in the queries and these are
the only three negative number result queries which stem from the same
original query. I do have other negative result queries that are working but
they are not returning data from the same base query. One other item of
interest is that when I try to reselect the table through table definition in
MS Query, I get !Unrecognised type "DECIMAL" in table. The sources for that
query are all set at standard and 2 places. I keep trying to change that to
long integer or double and 2, but every time I run the make table to update
it, it recreates it as decimal. I'm not sure if that might have something to
do with it or not. Oh how I wish I could solve this mystery.

Hi CJoyce,

I am getting exactly the same error on column 4 for the same type of
query. I didn't see anything further on this so, I'm posting my sql in hopes
that you will be able to tell me what is wrong.

Well, I see no obvious problem with the query and the fact that msquery does
show results proves that. One thing to try though is to replace all single
quotes with the apropriate square brackets:

SELECT [Interest_Other Income Link].masked_glnum, [Interest_Other Income
Link].RTrim, [Interest_Other Income Link].gldes, [Interest_Other Income
Link].[PYActuals], [Interest_Other Income Link].JanCY, [Interest_Other Income
Link].FebCY, [Interest_Other Income Link].MarCY, [Interest_Other Income
Link].AprCY, [Interest_Other Income Link].MayCY, [Interest_Other Income
Link].JunCY, [Interest_Other Income Link].JulCY, [Interest_Other Income
Link].AugCY, [Interest_Other Income Link].SepCY, [Interest_Other Income
Link].OctCY, [Interest_Other Income Link].NovCY, [Interest_Other Income
Link].DecCY, [Interest_Other Income Link].[CY Budget]
FROM [Interest_Other Income Link] [Interest_Other Income Link]

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi CJoyce,

I tried your suggestion and I wish I could
tell you that that worked but it didn't.

What is the source of the data, Access, Excel, ...?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[CJoyce]

Excel query on Access query on Thomson Elite Enterprise (our accounting
system's database)

 

[Jan Karel Pieterse]

Hi CJoyce,

Excel query on Access query on Thomson Elite Enterprise (our accounting
system's database)

In that case, could you copy the full SQL from the Access query into MSQuery,
that way the syntax should be correct.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com