User Profile

RorschachUK · Feb 13 '08, 10:36 AM

Well the only way round it I can think of is creating and populating the table, then adding a field using ADOX, then using VBA in Excel over ADO to loop through each line of the dataset. Then I can run as many IIFs as I want, and plug the calculated values back into the DB.

If anyone has a more elegant solution for use in the future, I'd be interested to hear it. :)

RorschachUK · Feb 13 '08, 09:53 AM

Thanks for your input, that's another way of looking at the logic, however, it doesn't solve my problem, as I still get the error
"Run-time error '-2147217900 (80040e14)':
Undefined function 'functionName' in expression."

I wanted to check that I was implementing the custom function correctly (inside a module), and I came across this:
http://www.thescripts.com/forum/thread124351.html
"Basically,...

RorschachUK · Feb 12 '08, 03:29 PM

Good point, i'll switch to ampersande. Out of interest, I changed to ampersandes in this code and tested again, but no dice. ;)

RorschachUK · Feb 12 '08, 03:10 PM

Arbitrary decision, didn't think there was any difference between the two.

RorschachUK · Feb 12 '08, 02:40 PM

Unfortunately not, it contains confidential information.

Any help is of course appreciated. :)

RorschachUK · Feb 12 '08, 02:04 PM

I'm using it in SQL, not VBA, as in your case, MTB.

A simplified version:

Code:

Public Sub MakeCallTable()

    Dim adoConnection As ADODB.Connection
    Dim adoxCatalog As ADOX.Catalog
    Dim cmdQueryMake As ADODB.Command
    Dim recSet As ADODB.Recordset
    Dim adoxProc As ADOX.Procedure
    Dim adoxTable As ADOX.Table
    Dim strYear As String
    Dim strDatabasePath

...

RorschachUK · Feb 12 '08, 12:35 PM

Thankyou for your reply.

I've created a MyFunctions module containing a public function containing the relevant logic. This also works fine when called directly from the Access GUI, no problems there.

However, once again, a problem crops up when trying to call this function over ADO from VBA. The error message states "Undefined function 'Blah' in espression."
This is when constructing the SQL query...

RorschachUK · Feb 11 '08, 12:54 PM

Apologies, found http://www.thescripts. com/forum/thread192015.ht ml just after I posted.
And I quote
"IIF is a VBA function which means that when this query is run in
Access it goes through th expression service in order to evaluate the
function, this doesn't happen when run through DAO."

If this is also true for ADO, that might explain it. So, is there no other solution other than rewriting my...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

IIF() logic failure over ADO