asked on

Type Mismatch in MySQL

Hi,

I am in the process of migrating from Access to MySQL and the following is a snippet of code that used to work but now doesn't.

Do Until objRec.EOF
curRCValTotal = curRCValTotal + objRec.Fields("fldRCVal")
objRec.MoveNext
Loop

I get the following error:

Microsoft VBScript runtime (0x800A000D)
Type mismatch

MySQL doesn't have a currency datatype like Access did so it is set to type 'decimal(10,2)' I have checked the data type of the recordset field ('fldRCVal') and the result is '14' or VB decimal. I have tried converting the datatype to currency like so:

CCur(curRCValTotal) = CCur(curRCValTotal) + CCur(objRec.Fields("fldRCVal"))

but I get the following error:

Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'CCur'

Any suggestions?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

L00M

Often you will get this error if the fldRCVal field is set to NULL
To overcome, try the following:

curRCValTotal = CCur("0" & Trim(curRCValTotal)) + CCur("0" & Trim(objRec.Fields("fldRCVal")))

That adds a zero to the beginning of the value. So, 1.45 becomes 01.45 and is translated correctly. NULL becomes "0" and translates correctly.

Ryan Chong

I think Trim(curRCValTotal) may error too if curRCValTotal contains NULL value, so maybe this better...

curRCValTotal = CCur("0" & Trim("" & curRCValTotal)) + CCur("0" & Trim("" & objRec.Fields("fldRCVal")))

or maybe...

curRCValTotal = 0
if isnumeric(objRec.Fields("fldRCVal")) then
curRCValTotal = curRCValTotal + objRec.Fields("fldRCVal")
end if

...