Undefined Function

[-=XQ=-]

I was under the impression you could write a public function in a MS Access module and then use it in your SQL statements... such as format(expression,"format") which works fine for me.

However I have wrote a function

Public Function ReturnTotBooked(ByVal lngQuotaID As Long) As Long

When I try to open the following SQL statement as a recordset using ADO:

SELECT tblQuota.lngSlot,format(MAX(dmyFrom),""hh:nn"") AS [From],
format(MAX(dmyTo),""hh:nn"") AS [To],SUM(intSlots) AS [Total Quota],
ReturnTotBooked([lngQuota]) AS [Total Booked],(SUM(intSlots) - [Total Booked]) AS [Available Slots] 
FROM (tblQuota INNER JOIN tblTimeslots ON tblQuota.lngSlot=tblTimeslots.lngSlot) 
WHERE lngArea=" & Right(lsvAreas.SelectedItem.Key, Len(lsvAreas.SelectedItem.Key) - 1) & _
" AND (dmyDate=DateValue('" & Format(txtDate.Value, "dd/mm/yy") & "')) 
GROUP BY tblQuota.lngSlot

When it is called I get the error function undefined. Does anybody know if there is a reason for this? Is it possible to call VB Functions you write yourself thru SQL?

Thanks for any advice!

[kleinma]

nope... this will not work.. when you make an SQL statement... you are making a string that is sent to the database to return a query(recordset)

what you should do is take that function out of your SQL statement... and call it when needed... so lets say you use that recordset value somewhere else you could do

msgbox ReturnTotBooked(rs.fields("lngQuota"))

[-=XQ=-]

This was a solution that was on a reference site for MS Access SQL. I admit I have never seen this used before and it was only a work around for another problem.

If you are sure that this cannot be done I am happy. Whilst I have you attention don't suppose you know anything about this post http://www.vbforums.com/showthread.php?threadid=200094

Thanks for your reply.