IIF() logic failure over ADO

[RorschachUK]

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

[RorschachUK]

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 SQL to not include IFF()? That's rather frustrating, and a little counter-intuitive... :P

[ADezii]

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 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.

[RorschachUK]

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.Fie ldname1) 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...

[MikeTheBike]

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.Fie ldname1) 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

Sub test()
Dim rs As New ADODB.Recordset
Dim Condition As Boolean

Condition = False

rs.Open "SELECT Surname FROM tblEmployees WHERE ID = " & IIf(Condition, 25, 33), CurrentProject.Connection

MsgBox rs(0)

End Sub

??

MTB

[RorschachUK]

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

A simplified version:

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 As String

    strYear = "2008"

    strDatabasePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='F:\Databases\db1.mdb';"

    Set adoConnection = New ADODB.Connection
    With adoConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = strDatabasePath
        .Open
    End With

    Set adoxCatalog = New ADOX.Catalog
    Set adoxCatalog.ActiveConnection = adoConnection

    For Each adoxProc In adoxCatalog.Procedures
        If adoxProc.Name = "MasterQueryCalls" Then
            adoxCatalog.Procedures.Delete "MasterQueryCalls"
        End If
    Next

    Set cmdQueryMake = New ADODB.Command
    Set cmdQueryMake.ActiveConnection = adoConnection

    cmdQueryMake.CommandText = "SELECT Call.CallNo, Call.Area " + _
        "IIf(Call.Area Like 'Canterbury*','Canterbury'," + _
        "IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location " + _
        "INTO CallTable FROM Call " + _
        "WHERE ((Call.Year)=" + strYear + ");"

    For Each adoxTable In adoxCatalog.Tables
        If adoxTable.Name = "CallTable" Then
            adoxCatalog.Tables.Delete "CallTable"
        End If
    Next

    cmdQueryMake.Execute
    adoConnection.Close
    Set adoConnection = Nothing
    Set adoxCatalog = Nothing
End Sub

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.

SELECT Call.CallNo, Call.CallArea,
    IIf(Call.Area Like 'Canterbury*','Canterbury',IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location INTO CallTable
FROM Call
WHERE ((Call.Year)=2008);

[ADezii]

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

A simplified version:

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 As String

    strYear = "2008"

    strDatabasePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='F:\Databases\db1.mdb';"

    Set adoConnection = New ADODB.Connection
    With adoConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = strDatabasePath
        .Open
    End With

    Set adoxCatalog = New ADOX.Catalog
    Set adoxCatalog.ActiveConnection = adoConnection

    For Each adoxProc In adoxCatalog.Procedures
        If adoxProc.Name = "MasterQueryCalls" Then
            adoxCatalog.Procedures.Delete "MasterQueryCalls"
        End If
    Next

    Set cmdQueryMake = New ADODB.Command
    Set cmdQueryMake.ActiveConnection = adoConnection

    cmdQueryMake.CommandText = "SELECT Call.CallNo, Call.Area " + _
        "IIf(Call.Area Like 'Canterbury*','Canterbury'," + _
        "IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location " + _
        "INTO CallTable FROM Call " + _
        "WHERE ((Call.Year)=" + strYear + ");"

    For Each adoxTable In adoxCatalog.Tables
        If adoxTable.Name = "CallTable" Then
            adoxCatalog.Tables.Delete "CallTable"
        End If
    Next

    cmdQueryMake.Execute
    adoConnection.Close
    Set adoConnection = Nothing
    Set adoxCatalog = Nothing
End Sub

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.

SELECT Call.CallNo, Call.CallArea,
    IIf(Call.Area Like 'Canterbury*','Canterbury',IIf(Call.Area Like 'Dover*','Dover','Thanet')) AS Location INTO CallTable
FROM Call
WHERE ((Call.Year)=2008);

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.

[RorschachUK]

Unfortunately not, it contains confidential information.

Any help is of course appreciated. :)

[Scott Price]

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

[RorschachUK]

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

[Scott Price]

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

[Scott Price]

Here´s a link that discusses the differences;

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

Regards,
Scott

[RorschachUK]

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

[ADezii]

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 (&):
   [CODE=vb]Debug.Print "John " & Null & "Doe" ==> John Doe[/CODE]
2. The use of the Plus (+) Operator:
   [CODE=vb]Debug.Print "John " + Null + "Doe" ==> Null
   [/CODE]

[ADezii]

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.Fie ldname1) 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?
[CODE=vb]
Dim cmd As ADODB.Command, lngRecordsAffec ted As Long

Set cmd = New ADODB.Command

cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "SELECT tblEmployeePFD. LastName, tblEmployeePFD. FirstName, " & _
"fEvaluateZip(t blEmployeePFD.Z ipCode) As [New Zip Code] " & _
"INTO Employee2 FROM tblEmployeePFD; "

cmd.Execute lngRecordsAffec ted[/CODE]
[CODE=vb]
Public Function fEvaluateZip(ln gZipCode)
Select Case lngZipCode
Case 45
fEvaluateZip = 999
Case 46
fEvaluateZip = 888
Case Else
fEvaluateZip = lngZipCode
End Select
End Function[/CODE]