ROUND() function in ado SQL statement?
Using VB6, ADO libraries 2.8... its wierd...
When I try to use the ROUN() function in my sql statement, I'm getting -2147217900 "undefined function ROUND in expression".
I found one other question on here related to this... one answer was to make sure that all the needed library references are included in the project, the other, and accepted, anser was to convert the value it INT and it would round as part of that.
I thought I had all the needed libraries referenced... this is the only command in my sql thats not working as of now. Does anyone know which library would need to be included in order for the ROUND funtion to be recognized?
CINT isn't useful... I need 2 decimal places. I spose I could multiply the value by 100, cint and then divide again, but thats not a very elegant solution...
Any insights will be appreciated..
When I try to use the ROUN() function in my sql statement, I'm getting -2147217900 "undefined function ROUND in expression".
I found one other question on here related to this... one answer was to make sure that all the needed library references are included in the project, the other, and accepted, anser was to convert the value it INT and it would round as part of that.
I thought I had all the needed libraries referenced... this is the only command in my sql thats not working as of now. Does anyone know which library would need to be included in order for the ROUND funtion to be recognized?
CINT isn't useful... I need 2 decimal places. I spose I could multiply the value by 100, cint and then divide again, but thats not a very elegant solution...
Any insights will be appreciated..
Hi alanvranian,
You can't use vb6- or user-functions within sql-statements... you'll have to use your workaround or do the rounding after querying the database.
You can't use vb6- or user-functions within sql-statements... you'll have to use your workaround or do the rounding after querying the database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
T-SQL ROUND function has 2 required parameters, the second is "Precision" (called "Length"). It also has a 3rd parm to determine "Truncate" or "Round".
Carlisle
Carlisle
ASKER
Thanks for your comments. Softplus, Your advice is good. On this one I did check and ROUND()is a valid t-sql function according to the sql 2000 developer's guide and this is the first time I've found an sql function in there that the jet 40 driver with access couldn't handle. So I suspect you've pointed out the answer... that this is a difference between the sql odbc driver and the jet 4.0 engine. Hmmm... I wonder how the ms ODBC driver for access would handle this... will have to play with that. At any rate, I suspect you've earned the points but if you dont mind I'd like to leave this open for a bit to see if anyone else knows some more difinitive and different.
If anyone knows where one can get a complete language reference for the Jet 4 engine, that would sure be a welcome discovery for me!
CarlislePh, thanks. My actual function code did include the precision parameter as in: Round(x,2). Even with that it won't work. But it was a good point to remind since I just fixed a bug in the code where I did indeed forget to add that parameter and the code was evaluating 1.06 to be equal to 1.31 and having me scratching my head for a few minutes!
Whats that old poem? Ah yes: "I really hate this damned machine, I wish that they would sell it. It never does what I want, but only what I tell it."
If anyone knows where one can get a complete language reference for the Jet 4 engine, that would sure be a welcome discovery for me!
CarlislePh, thanks. My actual function code did include the precision parameter as in: Round(x,2). Even with that it won't work. But it was a good point to remind since I just fixed a bug in the code where I did indeed forget to add that parameter and the code was evaluating 1.06 to be equal to 1.31 and having me scratching my head for a few minutes!
Whats that old poem? Ah yes: "I really hate this damned machine, I wish that they would sell it. It never does what I want, but only what I tell it."
Fundamental Access SQL :) -- by Microsoft:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp
there must be a real reference guide out there ... hmmm...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp
there must be a real reference guide out there ... hmmm...
>> and this is the first time I've found an sql function in there that the jet 40 driver with access couldn't handle.<<
Than you have not looked very hard, they are significant differences between T-SQL and the dialect of SQL used by MS Access. If you are using a reference to T-SQL in order to use SQL against an MS Access database, you are in for many more surprises.
Than you have not looked very hard, they are significant differences between T-SQL and the dialect of SQL used by MS Access. If you are using a reference to T-SQL in order to use SQL against an MS Access database, you are in for many more surprises.
ASKER
LOL, acperkins... now you really have me nervous ;) So far, its been OK. But I admittedly don't do queries that are particularly complex... just your usual select, etc etc.
ASKER
SOftplus, thanks for the links! They'll have some useful stuff, I'm sure! So here are your points :)
ASKER