Using math functions in Sql with MS Access

sql ms-access ms-access-2007 ms-jet-ace
simplfuzz picture simplfuzz · Aug 7, 2009 · Viewed 14.8k times · Source

I designed a query in SQL View using MS Access:

select floor(num1) from t1;

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

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

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

Answer

David-W-Fenton picture David-W-Fenton · Aug 8, 2009

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.

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 :(

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.

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.

Related questions

A select query selecting a select statement

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread). So I made a query that combines all of the …

Read More
How do I correctly use "Not Equal" in MS Access?

Objective: The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table. Current Query: SELECT DISTINCT Table1.Column1 FROM Table2, Table1 WHERE Table1.Column1 &…

Read More
Check if access table exists

I want to log web site visits' IP, datetime, client and refferer data to access database but I'm planning to log every days log data in separate tables in example logs for 06.06.2010 will be logged in 2010_06_06 named table. When date …

Read More