SUM Query Data Type Mismatch

[The Archn00b]

Hello everyone,

I think this is a vba question at heart so I've posted this here.

I'm trying to use this in a query:


SELECT

.

, SUM(CDbl([Table].[Field])) AS [New Field]
FROM Table
GROUP BY [Table].[Code][/B]


The CDbl() is used because the original value for [Table].[Field] is of the data type TEXT in Access (I'm connecting to an ODBC database). Of course it has to be a number so I've used CDbl() to convert the value.

However I get an error message:

[B]"Data type mismatch in the criteria expression" [/B]

Which I think is something to do with the 

[CODE][B]SUM(CDbl([Table].[Field]))[/B]

Does anyone have any ideas?

Thanks!

 

[plog]

Rule, #1 of data--don't trust your data. If I had an extra millimeter of length and girth everytime I heard data is "supposed to be" this or that, I'd be a famous porn star. Never assume your data is what you think it is, especially if you aren't using the correct data type for it.

Most likely you have non-numeric data in [Field]. Run this query and see if it returns any results:

SELECT [Field]
FROM Table
WHERE (IsNumeric([Field])=False);

It will return all non-numeric [Field] values. Those are the ones causing your query to snafu.

If [Field] is supposed to be numeric, force it to be by making its datatype the proper type.

 

[JHB]

Do you really have a table called Table and a field called Field, (if yes rename them because they are key words in MS-Access and shouldn't be used)?

 

[The Archn00b]

Rule, #1 of data--don't trust your data. If I had an extra millimeter of length and girth everytime I heard data is "supposed to be" this or that, I'd be a famous porn star.

lol

And that method worked for me. There were two fields that were still non-numeric because of the limitations of an underlying query. Thanks very much. Excellent work!