Using math functions in Sql with MS Access

Question

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 1

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.

Answer 2

Public Function Floor(ByVal x As Double) As Double
'Be Because VBA does not have a Floor function.
'Works for positive numbers
'Turns 3.9 -> 3
'Note: Round(3.9) = 4

    Dim s As String, dPos As Integer
    s = CStr(x)
    dPos = InStr(s, ".")
    Floor = CLng(Left(s, dPos - 1))
End Function

Answer 3

As mentioned, Floor isn't available in access, you should use int() as an alternative. If you insist on using Floor, you could always create a vba module function in your mdb file similar to below but that is probably overkill.

Public Function floor(dblIn As Double, dec As Integer) As Double
  decPosition = InStr(Str(dblIn), ".")
  x = Left(dblIn, decPosition + dec - 1)
  floor = x
End Function

Similar to the other math operations you described above you may create additional functions to create this set of functionality.