ODBC Invalid Character Value for Cast Specification Error

[kiwidancer]

Hello,
I am using VBA to query data from Access.

I have figured out that I am getting this error anytime the query needs to return a line that has the value of 0 in column 17.

The values are indeed 0, not null or blank and the same query works fine when I use MSQuery - the error only comes up when it needs to return the information to Excel.

My code for the query looks like this:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC; DSN=MS Access Database; DBQ=\\SERVIDOR\CELUPAL55\CELUPAL55.mdb; DriverId=25;FIL=MS Access; MaxBufferSize=2048; PageTimeout=5;", Destination:=Range(rng)).QueryTable
.CommandText = "SELECT * FROM MonthInvoices WHERE (`Invoice Date`>=20091201 And `Invoice Date`<=20091231)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With

if I change the .CommandText line to:
.CommandText = "SELECT * FROM MonthInvoices WHERE (`Func Unit Cost`<>0) AND (`Invoice Date`>=20091201 And `Invoice Date`<=20091231)

then I get no error.

Is there any way to SET the cast specification to what I want it to be? Or some other workaround to include these lines as my report is not correct without them?

Thank you in advance for any help!

 

[SkipVought]

Hi,

You gave us no information regarding the data in the table. So how can we venture a suggestion, not knowing the nature of your data.

I have seen instances in corporate databases, whee invalid data makes a simple query impossible. You'll need to ANALYSE the data to determine if there are invalid records.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kiwidancer]

The column that it's having a problem with has a numerical type, and 0 is in fact a numerical type.

If the records were invalid, then wouldn't there be a problem when doing the query in MSQuery as well, not only in Excel??

Below is a sample line of the data that causes it to error.

http://docs.google.com/leaf?id=0Bz0ft9Qhx-IFNmU2ZmZjZWQtNmFlYS00YzlhLTliNDktNjU5NWZhYTkxMTdh&hl=en

 

[SkipVought]

If the records were invalid, then wouldn't there be a problem when doing the query in MSQuery as well, not only in Excel??

I am confused! Is you code not MS Query in Excel?


Have you examined ALL the data in that column in Access?

Can you do a query like this...

Select Distinct `Func Unit Cost`
FROM MonthInvoices

and if so, is the resultset instructive?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

What is "MidSalesPrice" ? Is it a calc?

Would it error if your FuncUnitCost = 0?

If so, that is likely to be the culprit...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kiwidancer]

Skip - yes, the code is msquery in excel, but what I'm trying to say is that when I actually open MSQuery and run the same query, it does not error - it only errors when it tries to write the data to excel.

I have examined all of the data in the column, i have even tried to pull up each invoice that has a functional unit cost of 0 individually and every one of them errors, but no other invoices do.

I tried your query directly from vba and it errored (because one of the values was 0) and then directly from msquery and it didn't tell me anything that I didn't already know...

xlbo - Mid Sales Price is a calculation, but it has nothing to do with the functional unit cost...


really, thank you guys for your input, but this problem is still driving me mad!

 

[PHV]

Mid Sales Price is a calculation
Which expression ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiwidancer]

ICPRIC.MARKUPCOST*(1+ICPRIC.PRCNTLVL2/100) AS [Mid Sales Price]


this column has zero or blank values for many more lines than I am having problems with

 

[jsteph]

You may want to try wrapping the calc in a ccur() and NZ()
ccur(nz(ICPRIC.MARKUPCOST*(1+ICPRIC.PRCNTLVL2/100),0))
--jim