Sum(CDbl()) Data Mismatching

[RemyS]

I have an INSERT INTO Query which is based on a string only import table; SELECT Sum(CDbl(Mid([IMPORT_TABLE].[Field1],6,12))) AS Amount

Without the Totals I am able to verify that the string is being successfully converted into a Double type Number, but when I try to add the Totals and select the Sum for the expression the Query Errors with Data type mismatch in criteria expression. I have been able to confirm that it is the inclusion of the Totals Sum that causes the error.

The query has been running successfully for months, until now.


Does anyone have any ideas as to why this is happening, or how to remedy it?
Thanks,
Remy 101 ways to do it with VB, and learning new ways every day.

 

[raskew]

Suspect the problem is with your data. If an instance of Mid([IMPORT_TABLE].[Field1],6,12) doesn't convert to
a number, this error would seem to occur.

I tested it with a field containing five-character zip codes.
It worked fine until I placed a space within the field, then
the same error was generated.

 

[RemyS]

Indeed my data is preceeded with spaces;' 45,000.00' though these spaces were never an issue before.

I have tried inserting a Trim() function to remove any spaces so that the expression would look like:
Sum(CDbl(Trim(Mid([IMPORT_TABLE].[Field1],6,12))))
but this still returned the Data mismatch error.

Could it have anything to do with my reference library? 101 ways to do it with VB, and learning new ways every day.

 

[machiko]

Hi I cannot answer your question, I am sorry. But perhaps you can answer mine which is related. I am trying to run a sql sum statement with the data submitted from a form.

Tha db table is called results and there are 4 columns:

ID salesperson sales commission

I want the user to be able to select the salesperson from a dropdwon form and then for the querey to write the sum of the comission for the selected salesman.

I have tried:

"select sum(commission) as mytotal from results where salesman = request.form("salesperson&quot"
response.write request.form("salesperson&quot
response.write rs = ("mytotal&quot

but it does not work. Can anybody help?

Thank you, machiko

 

[RemyS]

Hi Machiko

It looks like you're trying this in VBScript.
I'm sorry I don't yet know enough about that to help,

but I would recommend you start a new thread in this forum, and in the ASP forum.

Good luck Hundreds of ways to do things with VB, and learning new ways every day.

 

[raskew]

remys-

It's the comma. Get rid of it and your process should work.

Bob