By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

IIF() logic failure over ADO

P: 9
I'm having a problem when trying to pull data out of an Access database to plug into an excel spreadsheet with VBA.

It's all working fine, apart from a nested IFF() statement, which is working fine when I run it manually in Access, but when I try to run it from VBA over ADO, it evaluates as if all the expressions are false (not the case).

I tried creating the query from VBA using ADOX to make sure the syntax was correct, but once again, it evaluates incorrectly over ADO, and is fine run from Access's GUI. Has anyone heard of/encountered this problem before, or have tips on how else I might tackle this problem?

Thanks in advance.

Versions:
MS Access 2003 (11.6566.8132) SP2
MS VBA: Retail 6.4.9972
ADO/ADOX v2.8
Feb 11 '08 #1
Share this Question
Share on Google+
18 Replies


P: 9
Apologies, found http://www.thescripts.com/forum/thread192015.html 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 SQL to not include IFF()? That's rather frustrating, and a little counter-intuitive... :P
Feb 11 '08 #2

ADezii
Expert 5K+
P: 8,638
Apologies, found http://www.thescripts.com/forum/thread192015.html 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 SQL to not include IFF()? That's rather frustrating, and a little counter-intuitive... :P
Replace IIF() with the standard If...Else...End If Construct within a Public Function, then use the Function within the SQL Statement to return the proper result.
Feb 11 '08 #3

P: 9
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 in VBA.
I also tried making a simple Select... Into query that copies the primary table, incase it was writing the SQL in VBA that was causing the problem...

SELECT Blah(Table1.Fieldname1) AS Fieldname2, * INTO CallTable2
FROM CallTable;

But once again, I get the same undefined function 'blah' error message.

I presume this is because the JET engine is unaware of the custom function. Does anyone know if there's a way of inserting this definition?

Such a frustrating quirk, I'm beginning to believe I'll have to grab the recordset, dump it into Excel and process the data there, ugly and slow as that'd be...
Feb 12 '08 #4

Expert 100+
P: 634
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 in VBA.
I also tried making a simple Select... Into query that copies the primary table, incase it was writing the SQL in VBA that was causing the problem...

SELECT Blah(Table1.Fieldname1) AS Fieldname2, * INTO CallTable2
FROM CallTable;

But once again, I get the same undefined function 'blah' error message.

I presume this is because the JET engine is unaware of the custom function. Does anyone know if there's a way of inserting this definition?

Such a frustrating quirk, I'm beginning to believe I'll have to grab the recordset, dump it into Excel and process the data there, ugly and slow as that'd be...
Hi

I'm not sure how you are using IIF() but it seem to work with this query string
Expand|Select|Wrap|Line Numbers
  1. Sub test()
  2. Dim rs As New ADODB.Recordset
  3. Dim Condition As Boolean
  4.  
  5. Condition = False
  6.  
  7. rs.Open "SELECT Surname FROM tblEmployees WHERE ID = " & IIf(Condition, 25, 33), CurrentProject.Connection
  8.  
  9. MsgBox rs(0)
  10.  
  11. End Sub
??

MTB
Feb 12 '08 #5

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

A simplified version:
Expand|Select|Wrap|Line Numbers
  1. Public Sub MakeCallTable()
  2.  
  3.     Dim adoConnection As ADODB.Connection
  4.     Dim adoxCatalog As ADOX.Catalog
  5.     Dim cmdQueryMake As ADODB.Command
  6.     Dim recSet As ADODB.Recordset
  7.     Dim adoxProc As ADOX.Procedure
  8.     Dim adoxTable As ADOX.Table
  9.     Dim strYear As String
  10.     Dim strDatabasePath As String
  11.  
  12.     strYear = "2008"
  13.  
  14.     strDatabasePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='F:\Databases\db1.mdb';"
  15.  
  16.     Set adoConnection = New ADODB.Connection
  17.     With adoConnection
  18.         .Provider = "Microsoft.Jet.OLEDB.4.0"
  19.         .ConnectionString = strDatabasePath
  20.         .Open
  21.     End With
  22.  
  23.     Set adoxCatalog = New ADOX.Catalog
  24.     Set adoxCatalog.ActiveConnection = adoConnection
  25.  
  26.     For Each adoxProc In adoxCatalog.Procedures
  27.         If adoxProc.Name = "MasterQueryCalls" Then
  28.             adoxCatalog.Procedures.Delete "MasterQueryCalls"
  29.         End If
  30.     Next
  31.  
  32.     Set cmdQueryMake = New ADODB.Command
  33.     Set cmdQueryMake.ActiveConnection = adoConnection
  34.  
  35.     cmdQueryMake.CommandText = "SELECT Call.CallNo, Call.Area " + _
  36.         "IIf(Call.Area Like 'Canterbury*','Canterbury'," + _
  37.         "IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location " + _
  38.         "INTO CallTable FROM Call " + _
  39.         "WHERE ((Call.Year)=" + strYear + ");"
  40.  
  41.     For Each adoxTable In adoxCatalog.Tables
  42.         If adoxTable.Name = "CallTable" Then
  43.             adoxCatalog.Tables.Delete "CallTable"
  44.         End If
  45.     Next
  46.  
  47.     cmdQueryMake.Execute
  48.     adoConnection.Close
  49.     Set adoConnection = Nothing
  50.     Set adoxCatalog = Nothing
  51. End Sub
  52.  
The Location column evaluates to "Thanet" in every case when this is run.

However, when I paste that SQL into an Access query, and strip out the VBA formating, it works fine.
Expand|Select|Wrap|Line Numbers
  1. SELECT Call.CallNo, Call.CallArea,
  2.     IIf(Call.Area Like 'Canterbury*','Canterbury',IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location INTO CallTable
  3. FROM Call
  4. WHERE ((Call.Year)=2008);
  5.  
Feb 12 '08 #6

ADezii
Expert 5K+
P: 8,638
I'm using it in SQL, not VBA, as in your case, MTB.

A simplified version:
Expand|Select|Wrap|Line Numbers
  1. Public Sub MakeCallTable()
  2.  
  3.     Dim adoConnection As ADODB.Connection
  4.     Dim adoxCatalog As ADOX.Catalog
  5.     Dim cmdQueryMake As ADODB.Command
  6.     Dim recSet As ADODB.Recordset
  7.     Dim adoxProc As ADOX.Procedure
  8.     Dim adoxTable As ADOX.Table
  9.     Dim strYear As String
  10.     Dim strDatabasePath As String
  11.  
  12.     strYear = "2008"
  13.  
  14.     strDatabasePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='F:\Databases\db1.mdb';"
  15.  
  16.     Set adoConnection = New ADODB.Connection
  17.     With adoConnection
  18.         .Provider = "Microsoft.Jet.OLEDB.4.0"
  19.         .ConnectionString = strDatabasePath
  20.         .Open
  21.     End With
  22.  
  23.     Set adoxCatalog = New ADOX.Catalog
  24.     Set adoxCatalog.ActiveConnection = adoConnection
  25.  
  26.     For Each adoxProc In adoxCatalog.Procedures
  27.         If adoxProc.Name = "MasterQueryCalls" Then
  28.             adoxCatalog.Procedures.Delete "MasterQueryCalls"
  29.         End If
  30.     Next
  31.  
  32.     Set cmdQueryMake = New ADODB.Command
  33.     Set cmdQueryMake.ActiveConnection = adoConnection
  34.  
  35.     cmdQueryMake.CommandText = "SELECT Call.CallNo, Call.Area " + _
  36.         "IIf(Call.Area Like 'Canterbury*','Canterbury'," + _
  37.         "IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location " + _
  38.         "INTO CallTable FROM Call " + _
  39.         "WHERE ((Call.Year)=" + strYear + ");"
  40.  
  41.     For Each adoxTable In adoxCatalog.Tables
  42.         If adoxTable.Name = "CallTable" Then
  43.             adoxCatalog.Tables.Delete "CallTable"
  44.         End If
  45.     Next
  46.  
  47.     cmdQueryMake.Execute
  48.     adoConnection.Close
  49.     Set adoConnection = Nothing
  50.     Set adoxCatalog = Nothing
  51. End Sub
  52.  
The Location column evaluates to "Thanet" in every case when this is run.

However, when I paste that SQL into an Access query, and strip out the VBA formating, it works fine.
Expand|Select|Wrap|Line Numbers
  1. SELECT Call.CallNo, Call.CallArea,
  2.     IIf(Call.Area Like 'Canterbury*','Canterbury',IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location INTO CallTable
  3. FROM Call
  4. WHERE ((Call.Year)=2008);
  5.  
I imagine that there would be no way in which you could E-Mail me the Database as an Attachment, so as soon as I get the chance I'll try to parallel exactly what you are doing and hopefully pinpoint the problem. Will get back to you on this.
Feb 12 '08 #7

P: 9
Unfortunately not, it contains confidential information.

Any help is of course appreciated. :)
Feb 12 '08 #8

Scott Price
Expert 100+
P: 1,384
Unfortunately not, it contains confidential information.

Any help is of course appreciated. :)
Just a quick question, is there a reason you are using the Plus symbol concatenation operator instead of the more common Ampersand?

Regards,
Scott
Feb 12 '08 #9

P: 9
Arbitrary decision, didn't think there was any difference between the two.
Feb 12 '08 #10

Scott Price
Expert 100+
P: 1,384
Arbitrary decision, didn't think there was any difference between the two.
Theoretically there shouldn´t be any difference, but since you´re getting unexpected results using them, I wonder if it would be worth changing them? I´m sorry to say I can´t remember right now what the actual difference is, but in some languages there is a slight difference in behavior between the two. Let me do a little research, I think there´s an article or thread here on the Scripts that has a bit more info.

Access supports using both, but other applications only support the ampersand. Thus, it´s a good practise to use it instead.

Regards,
Scott
Feb 12 '08 #11

Scott Price
Expert 100+
P: 1,384
Here´s a link that discusses the differences;

http://www.thescripts.com/forum/thread762181.html

Regards,
Scott
Feb 12 '08 #12

P: 9
Good point, i'll switch to ampersande. Out of interest, I changed to ampersandes in this code and tested again, but no dice. ;)
Feb 12 '08 #13

ADezii
Expert 5K+
P: 8,638
Just a quick question, is there a reason you are using the Plus symbol concatenation operator instead of the more common Ampersand?

Regards,
Scott
Hello Scott and congratulations again on your assignment. There is, in fact, a distinct difference between the use of "+" and "&" and it has to do with Null Propagation. Using "+" within a Statement effectively makes it an Expression, and as such will allow Nulls to propagate through it, whereas "&" will not. If 'any' part of an Expression evaluates to Null, the entire Expression will return Null. A simple case will illustrate this point:
  1. The use of the Ampersand (&):
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "John " & Null & "Doe" ==> John Doe
  2. The use of the Plus (+) Operator:
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print "John " + Null + "Doe" ==> Null
    2.  
Feb 12 '08 #14

ADezii
Expert 5K+
P: 8,638
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 in VBA.
I also tried making a simple Select... Into query that copies the primary table, incase it was writing the SQL in VBA that was causing the problem...

SELECT Blah(Table1.Fieldname1) AS Fieldname2, * INTO CallTable2
FROM CallTable;

But once again, I get the same undefined function 'blah' error message.

I presume this is because the JET engine is unaware of the custom function. Does anyone know if there's a way of inserting this definition?

Such a frustrating quirk, I'm beginning to believe I'll have to grab the recordset, dump it into Excel and process the data there, ugly and slow as that'd be...
I've reduced your code to its simplest state while still trying to maintain the original concept. The following code will create a New Table named Employees2 consisting of the [Last] and [First] Name Fields as well as a [New Zip Code] Field whose vales were dynamically created. Before the New Table was created the original [ZipCode] Field was passed to the Public Function fEvaluateZip() where it was analyzed and the proper values were returned. If the Zip was 45 then 999 was returned, if it was 46 then 888 was returned but if neither one of these values then the original [ZipCode] values populated the New Table. The code was thoroughly checked and the results were verified by the RecordsAffected Argument. One very important point to mention: If either the Return Value of the Function or the Argument contained within the Function were declared as Explicit Data Types, Errors resulted. This is why they are both defined as Variants. Does this help, or have I missed the point?
Expand|Select|Wrap|Line Numbers
  1. Dim cmd As ADODB.Command, lngRecordsAffected As Long
  2.  
  3. Set cmd = New ADODB.Command
  4.  
  5. cmd.ActiveConnection = CurrentProject.Connection
  6. cmd.CommandText = "SELECT tblEmployeePFD.LastName, tblEmployeePFD.FirstName, " & _
  7.                    "fEvaluateZip(tblEmployeePFD.ZipCode) As [New Zip Code] " & _
  8.                    "INTO Employee2 FROM tblEmployeePFD;"
  9.  
  10. cmd.Execute lngRecordsAffected
Expand|Select|Wrap|Line Numbers
  1. Public Function fEvaluateZip(lngZipCode)
  2. Select Case lngZipCode
  3.   Case 45
  4.     fEvaluateZip = 999
  5.   Case 46
  6.     fEvaluateZip = 888
  7.   Case Else
  8.     fEvaluateZip = lngZipCode
  9. End Select
  10. End Function
Feb 12 '08 #15

P: 9
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, you can divide an Access application into two parts: the
Graphical User Interface (GUI), which also include all VBA modules and
functions - and the Database part (tables, relations, etc.; also known as
DAO or the JET engine) which can be manipulated by the ODBC and OLEDB
drivers. The JET engine cannot run any VBA code; however, the GUI can run
JET, ask it to return all relevant records (which would mean *all* records
on many occasions) and then apply your VBA functions on the result.

The GUI part is only accessible from Access and cannot be manipulated
outside of it; which means that you cannot access your VBA code directly
from .NET; which also means that you cannot do (directly) what you want to
do. (The only possibility would be to open an Access application under IIS
on the server via COM Interoperability. However, not only this would be a
little complicated to realize but also would be a real performance hog when
executed on a web server under IIS.)"

This is what I'm doing; running code in Access over an ADO (the successor to DAO) link, instead of using Access' GUI.
It looks like this is what's screwing me over. I can't use commands such as IFF because it's inherited VBA code, and I can't use a custom VBA function as you suggest, for the same reason.

Unfortunately, unless I can find a way of using Access' native methods, it looks like I may have to fall back on dumping the raw data into Excel, and processing it there.

JET SQL doesn't support If...Else does it? Are there any other logic structures I could use? I'm running out of ideas. :)
Feb 13 '08 #16

P: 9
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. :)
Feb 13 '08 #17

ADezii
Expert 5K+
P: 8,638
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. :)
Just a couple of questions, RorschachUK:
  1. Please forgive me if I state the obvious, but are you Declaring the Public Function in a 'Standard Code Module' and not a 'Form's Class Module'?
  2. How about a reverse approach if all else fails? Do all the processing from Excel via Automation using Access as a Automation Client, bring the Recordset in via the CopyFromRecordset Method, then manipulate the data from within Excel. Forgive me if I'm ramblinng!
Feb 13 '08 #18

Scott Price
Expert 100+
P: 1,384
A very small minor point to clear up for anyone who stumbles across this thread in future:

The syntax is not IFF() with two F's, but IIF() with two I's... I notice you have it correct in your code, but repeatedly refer to it as IFF. I'll correct the title of this thread to make it searchable.

Regards,
Scott
Feb 13 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.