User Defined Functions Error ....

[haribabu]

Hi Friends,
I am working on Project Using VB 6.0 & MS Access 2000..
I am using ADO for Database connection...

Problem is while Executing the below qry, I am getting error...

Qury Name: qry_GenerateInvoice

In Access:-
"SELECT IIf(IsNull(Max([Sales]![InvoiceNo])),fnFormatDate(Date()),Max([Sales]![InvoiceNo]))+1 AS Expr1 FROM Sales WHERE (((Sales.InvoiceNo)>fnFormatDate(Date())));"


In query, fnFormatDate is Function written in Access Module...


While executing the query from VB, I am getting error Invalid SQL Statement expecting INSERT, UPDDATE OR SELECT STATEMENT.... While I further working on this, I am getting error - UNDEFINED FUNCTION 'fnFormatDate'EXPRESSION

How to solve this problem?
Mainly I need solution that whether I call functions of Access Module using Select Statement from VB 6.0

FYI: This Query work fine in Access
Thanxs in advance..

[dannymking]

It would be best if you showed us the code from the function..

I'm guessing that this is not a date returned value due to the Sales.InvoiceNo..

You can also reduce your SQL down to..

"SELECT IIf(IsNull(Max(InvoiceNo)),fnFormatDate(Date()),Max(InvoiceNo))+1 AS Expr1 FROM Sales WHERE InvoiceNo>fnFormatDate(Date());"

[Ecniv]

It works in access, because in access you can call custom functions in the sql statement.

However in VB either it doesn't let you use custom functions in the access database, or your custom function is in the vb side and not in the access mdb side, so it cannot find it.