number format in sql queries

[Maximnl]

I was about to post a question yesterday, but I found a solution myself and would like to share it. When a new column is computed in the query, there is no way to prescribe the data format of the computed column (=field =variable). It is done automatically by access and it can go wrong. I tried to use Format() function but it changes the look but does not change the type of the column. So what you have to do is to wrap your expression in a data convertion function. I used CDBL() as a wrapper for my expression.

I consider it as a bug of Access because in my situation I had a swich() function that took only numeric fields and produced only number but the data type of the column was set to TEXT. I noticed it in the pivot table later on , when I could only use Count() as aggregate function.

Good luck! and take care of data conversion by yourself, do not let it go automatically!

P.S. If anyone knows a better way to prevent such conversion errors, be my guest!!!

By
Maxim Ivashkov
http://www.4suc6.com

 

[dcobau]

Hi Maxim,

I had a project, long ago, which required number crunching bit time done by a large number of queries with lots of formulas. I used functions such as CInt, CDbl and CLng to create new fields. Did you try those as well?

Dave

 

[Maximnl]

Cdbl does define numeric format in queries

Hi Dave,
thanks for you reply. I did discovered the CDbl()
It works, but it seems to me a weird way to control the format of a column. It could take extra CPU time to process exressions as well. Let's be happy that there is at least one method!