-1

I have used Excel and MS Access for many years but only now have I encountered this problem: I have a SELECT statement, where one of the fields is an alias that uses a User-Defined function to compute the ceiling of a field-value. Problem is that because it is an expression, the ADO connection will not allow Excel to access the object in Access. When I write the ADO connection as a sub module, having the SELECT statement execute via the connection string, function is NOT identifiable in the VBA Project - and rightfully so, it is in MS Access. Here is the SELECT statement:

SELECT Left([Description],50) AS Comment, 
   Ceiling(Avg([Task_Total])) AS Task, 
   TblTaskTotal.Dtd 
FROM TblTaskTotal 
GROUP BY Left([Description],50), TblTaskTotal.Dtd HAVING (((TblTaskTotal.Dtd)=Date())) 
ORDER BY Left([Description],50);

In Excel VBA Project, the resulted in run-time error ("Undefined function 'Ceiling' in expression". Any suggestions as to how to get around this Gremlin?

June7
  • 19,874
  • 8
  • 24
  • 34
Anthony Morris
  • 1
  • 2
  • I guess either write the calculated value to table or have Excel pull in raw data and do the calc in Excel. – June7 Jul 16 '20 at 23:02
  • `Round(Avg([Task_Total])+0.5, 0)` ? – Tim Williams Jul 17 '20 at 05:53
  • Thank you Tim. That is a possibility. However, I am using a table of un-normalized reference data to capture the Task Totals, which are averaged in the query object. This will create a huge list of raw data eventually, that will need to be cleaned up at intervals. This will be plan B. – Anthony Morris Jul 18 '20 at 00:53

2 Answers2

1

You can replace the missing Ceiling with the generic round-up method:

-Int(-Avg([Task_Total])) AS Task
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thank you Gustav - However, the problem with built-in 'RoundUp'; my version of MS Access does not recognize Roundup in the Query object. Run-time Error "Undefined function 'Roundup' in expression. – Anthony Morris Jul 18 '20 at 00:15
  • Ture, there is no "RoundUp" function. That's why I showed how to use native SQL to perform the round up. – Gustav Jul 18 '20 at 07:22
0

The issue was that I could not use ADO Connection to select the Query object due to the fact that the function was unidentifiable in MS Excel, as it was stored in MS Access; the query was not in the list of items to connect and retrieve the query object data from MS Access. The solution was to use the built-in MAX function rather the ceiling, which gives an equivalent value as the ceiling. E.g. X = 4.2387 where Ceiling(X) = 5.0. Likewise, out of Task Total data sample of Task_Total(2,3,3,4,6,13,14), the MAX=14, which is the number of Task for the given month. It means that I do not need to use an Expression. Thanks all for your suggestions.

Anthony Morris
  • 1
  • 2
  • This makes no sense. Max is very different from rounding up. It is not easy to advise when you suddenly change the requirement. – Gustav Jul 18 '20 at 07:25
  • What it came down to is - In a subset of an Active Project, the Task_Total with the highest number of Task, IS THE actual number of tasks, which is the MAX number in an active project's Un-normalized subset, of duplicate records. To the point - A Ceiling function was not the tool needed, nor the Roundup function. It was simpler than that, just needed the MAX function which does not require the query field to be an expression, and my testing proves it 100%. “THERE IS ALWAYS MORE THAN ONE WAY TO SKIN A CAT”. When it’s too hard to find the answer it’s probably right in front of your eyes. – Anthony Morris Jul 19 '20 at 13:02