Using Cdbl in an update query when there is a field mismatch issue.

[riffology]

Hi All,

I'm sure this is simple for most people, but I can't seem to figure out how to correct a mismatch issue for an update query. I have two tables, both of which contain an account number. In one table the account number is in text format and the other table the account number is in number format (I can't change the format in one table and it would be a hassle to change the format in the other table due to a multitude of queries). I know I can use CDbl to correct the mismatch issue, but I'm not sure how to use it. Could someone help?

[June7]

Maybe like:

SELECT *, CDbl([fieldname]) AS ActNumFixed FROM tablename;

[John_G]

An account number is usually an integer value, so using CDbl isn't really what you need. Use Clng instead.

In the update query you would use Clng(textvalue) to change it to numeric.

[June7]

Or use Val() and the specific number type shouldn't matter.

Be aware all of these number conversion functions will error on Null.

I think fixing the data type is better solution. Suggest you try this on a copy of the db.

Why are you using an UPDATE query? If the field type is text, what field are you updating with number?