通过MS Access在Sql中使用数学函数 [英] Using math functions in Sql with MS Access

问题描述

我使用MS Access在SQL View中设计了一个查询:

I designed a query in SQL View using MS Access:

select floor(num1) from t1;

运行它时，出现未定义的功能层".

When I run it, I get "undefined function floor".

对于Ceil，Mod，Power，Sign，Sqrt，Trunc和initcap函数，我也会遇到类似的错误.

I get similar errors for Ceil, Mod,Power, Sign, Sqrt, Trunc, and initcap functions.

Access数据库引擎的SQL语法是否具有等效功能?

Does the Access database engine's SQL syntax have equivalent functions?

推荐答案

用Int()替换Floor().我是通过在Access帮助文件中搜索(在这种情况下，在查询设计器中同时按F1并搜索功能")来学习的.那把我带到了一个比较VBA和T-SQL函数的帮助主题.

Replace Floor() with Int(). I learned this by searching in the Access help files, in this case, hitting F1 while in the query designer, and searching for "functions." That took me to a help topic comparing VBA and T-SQL functions.

您可能应该看看访问数据库引擎SQL参考.对于Jet/ACE和Access表达式服务支持的功能，我找不到很好的在线参考.由于某些未知原因，自Jet 3.0以来，Access Help尚未包含Jet/ACE表达式，并且此老化的资源在一两年前终于从MSDN中删除了:(

You should probably have a look at the Access database engine SQL Reference. I can't find a good online reference for functions that are supported through the Jet/ACE and Access expression services. For some unknown reason, the Access Help has not included Jet/ACE expressions since Jet 3.0 and this aged resource was finally removed from MSDN a year or two ago :(

请记住，在Access外部使用的Jet/ACE表达式服务支持的功能子集要小得多，而在Access 2007中运行SQL时，可以使用Access Expression Service来实现.在访问用户界面外部支持涉及简单数据类型(不同于数组或对象)的方法)；有关函数名称的大概列表，请参见这篇MSDN文章.

Keep in mind that the Jet/ACE expression service for use outside Access supports a much smaller subset of functions that is possible using the Access Expression Service when running your SQL inside Access 2007. Broadly speaking, the VBA5 functions (as distinct from methods) that involve simple data types (as distinct from, say, arrays or objects) are supported outside of the Access user interface; for an approximate list of function names see the 'Use Sandbox mode operations with Jet 4.0 Service Pack 3 and later' section of this MSDN article.

此外，VBE帮助中的功能参考也应该是一个起点.

Also, the functions reference in the VBE help should be a starting place.

帮助文件并不完美，但是稍作搜索就可以为您提供所需的信息.

The help files are not perfect, but a little searching ought to get you what you need.

这篇关于通过MS Access在Sql中使用数学函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！