HELP! Why would CDbl suddenly stop working??? 1

PenelopeC · Feb 12, 2004

Hi All,
I have an Access97 application working with linked SQL Server 2000 data. In Access I have a table where the currency field is padded with zeros so it can go into a mainframe.

To create a report based on the table, the currency field that's originally text is converted to a double using this syntax:

Sum(CDbl([journal_entry].[OS_AMOUNT]))

This is in some module code that makes another table altogether for the report to be run off of. When I try to run the code I get an ODBC error. I've narrowed it down to a data type conversion error which I figured out to be this field.

This has worked for the past three years but suddenly doesn't. (I last ran this routine on 1/15/04 and it worked fine.) What is going on??

Does this even make any sense? Please let me know if I'm completely not making any sense. Of course this is all a rush -- I'm holding up accounting with this problem.

TIA!!!

Penelope

PenelopeC
~~~>-/O~~~~~swimming right along

vbajock · Feb 12, 2004

Did you check for any broken references?

vbajock · Feb 12, 2004

Also, since you are getting an ODBC error, I imagine this:
Sum(CDbl([journal_entry].[OS_AMOUNT]))

is embedded in a query or SQL somewhere. I doubt if your problem has anything to do with the cDbl function. It sounds like one of the tables the query is based on has lost its ODBC link some how. Can you open the table by clicking on it in the database window, or if it is not in the database window can you link it to your database?

Have you gone into ODBC administration in the Control Panel? You should find the correct driver, walk it thru the config screens, and then hit the Test Connection button on the last screen.

PenelopeC · Feb 12, 2004

vbajock,
I don't have any missing references. Those would pop up right away right? (Going to an open code window then to Tools, References.)

All the tables I have linked I'm able to open.

Going to configure in the Control Panel, Administration, ODBC connections, Next ing through all the steps and then "Test Connection" works on both the User and System connections.

So, all those eliminated. The error message I get when I take the query out of the code and run it in a new query window gives me an error message about data type mismatch error.

I appreciate your help on this, I'm really quite beside myself not being able to figure this out.

Thanks so much -- any other ideas?

Penelope

PenelopeC
~~~>-/O~~~~~swimming right along

PenelopeC · Feb 12, 2004

The exact error message I get from the query is

First an error box pops up "ODBC error"

I click the OK button then,

[Microsoft][ODB SQL Server Driver][SQL Server]Error converting data type varchar to float. (#8114)

This is why I thought it had to do with the CDbl function.

Thanks,

PenelopeC
~~~>-/O~~~~~swimming right along

PenelopeC · Feb 12, 2004

vbajock,
Actually, I found a table that's missing. Only problem is I can't figure out where/how its created. Ack!!

PenelopeC
~~~>-/O~~~~~swimming right along

PenelopeC · Feb 16, 2004

This ended up being an error in the data. There was a dash (-) in a text field on one record...this gave me the ODBC error.

<voice dripping sarcasm> Nice job Microshaft, telling me exactly what the problem is in an error message.
</voice dripping sarcasm>

PenelopeC
~~~>-/O~~~~~swimming right along

vbajock · Feb 18, 2004

It's really not Microsoft's fault. Your trying to write character data to a numeric field. Good programming practice gives you the responsibilty to test your data first. This is the way I would handle it:

Instead of

Sum(CDbl([journal_entry].[OS_AMOUNT]))

use

iif isnumeric([journal_entry].[OS_AMOUNT]), Sum(CDbl([journal_entry].[OS_AMOUNT])),-99999)

Isnumeric evaluates to true or false. If the data contains anything other than 0-9 or decimal point, it evaluates to false. So we are saying, if the data is numeric, get me the data, else, plug in a tracking value.

then you could also add an additional field to your query
in the column header:

Expr1:iif not isnumeric([journal_entry].[OS_AMOUNT]), "BAD DATA ERROR",NUll)

In your report, you could then use either the tracking value of -99999 (could be any number not expected in your data) or the error in the error field in your reports to control the bad data.

PenelopeC · Feb 19, 2004

Alright, I concede its my job to check the data first.

The data type is never numeric though because the data is coming from a table with that field formatted as a text data value -- would your suggestion still work?

Thanks!

p.s. I didn't write this app. Its full of gotcha's like this because the programmer didn't stick around to write all the error checking like this. There are other examples of data slipping through that I don't know how to capture and error out -- I guess I need a tutorial on error trapping!

PenelopeC
~~~>-/O~~~~~swimming right along

vbajock · Feb 19, 2004

isnumeric() checks string data to see if it will convert to a valid number. Not only will it work, it is specifically designed for situations like yours. If the data in the text field is 39, it will return true. If it is 3-9, it will return false, because it contains a non-numeric character, the "-" sign.

PenelopeC · Feb 19, 2004

Super! I can think of a few different places to use this.

I appreciate your input.

PenelopeC
~~~>-/O~~~~~swimming right along

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

HELP! Why would CDbl suddenly stop working??? 1

PenelopeC

Technical User

vbajock

Programmer

vbajock

Programmer

PenelopeC

Technical User

PenelopeC

Technical User

PenelopeC

Technical User

PenelopeC

Technical User

vbajock

Programmer

PenelopeC

Technical User

vbajock

Programmer

PenelopeC

Technical User

Similar threads

Part and Inventory Search

Sponsor