I am using the following SQL statement going against an Access database:

SELECT *, (Cost/PricePer) as MAmt FROM Table WHERE Num = @Num

Which works, however, PricePer can be zero so I have tried the following statement to check to see if PricePer is zero and if it is, I want MAmt to be zero:

SELECT *, IIF(PricePer > 0, (Cost/PricePer), 0) as MAmt FROM Table WHERE Num = @Num

The returning recordset has "#ERROR" in the MAmt field. What am I missing in this SQL statement?

Perhaps the addition of the IIF statement then makes Access mistakenly misinterpret the slash in (Cost/PricePer). How about being more explicit?



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for your input. It turns out that I had to put the zero in glitches in order for it to work.

IIF(PricePer > '0', (Cost/PricePer), 0)

Glitches? :) I've heard "single quotes" and "apostrophes" but never "glitches". I'll have to remember that one.

If you have to put glitches on the zeroes that means that your Cost/PricePer field is a text field. Odd... shouldn't that be a number, in which case you wouldn't need the glitches.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

That's funny. I've worked in Information Technology services for over 10 years and all through out my time in college we referred to a single quote as a glitch.

The Cost/PricePer field ("MAmt") isn't an actual field in the database. It is a virtual field that is created in the recordset by the SQL statement.

What is the datatype of the combined "virtual field"?
In other words, what are all of the datatypes from all of the fields used to create this virtual field?



Sal

They are both numeric. I can't explain the need for the glitches. That's why I never even considered using them before, but that's what fixed it.

I'm much confused, data types aside.

It should be impossible to test for a divide-by-zero condition using IIf. Take for example:

Sub Test(numerator As Integer, denominator As Integer)
    Dim dblNew As Double

    dblNew = IIf(denominator > 0, numerator / denominator, 0)

End Sub

' Test IIf
Call Test 1,0


IIf always evaluates both the truepart and the falsepart of the expression. So, the above would yield:

dblNew = IIf(0 > 0, 1/0, 0)

or try

dblNew = IIf(0 = 0, 0, 1/0)

You would think the first example would return the falsepart (0). But it doesn't. When IIf executes, the truepart (1/0) is also always evaluated, and raises a divide by zero error if the denominator is 0. So you can't check for divide-by-zero using IIf in VBA. You'll always get Run-time error 11: Divide by zero, if the value of the 'denominator' is zero; the falsepart isn't returned.

Then I ran the following in Jet SQL:

SELECT Num, PricePer, Cost, IIf([PricePer]>0,([Cost]/[PricePer]),0) AS Amt
FROM [Table]
WHERE Num=2;

This works fine. Why? It should fail if PricePer is 0. Are the VBA IIf function and the Jet SQL IIf function not the same function? Weird.

The work-around I've always used in VBA to test for divide-by-0 is:

Sub Test1(numerator As Integer, denominator As Integer)
    Dim result As String

    result = Divide(numerator, denominator)

    'Value of result is "" if exit function executed
    If result = "" Then
        MsgBox "Divide by zero attempted"
    Else
        MsgBox result
    End If
End Sub

Function Divide(n As Integer, d As Integer) As String

        If d = 0 Then
            Exit Function 'result = ""
        Else
            Divide = n / d
        End If

End Function

Or I've called function Divide from a query. Can't figure out why IIf works when its embedded in SQL, but not in VBA.

Bob

Had a hunch. I have yet to stumble across one of these VB glitches (proper usage of the term "glitch" by the way), like the behavior of IIf, that C# doesn't resolve sensibly.

The problem is with the VB compiler. When VB compiles a call to IIf, it has to evaluate all three parts of the IIf (the condition, the true part and the false part) and then send them to the IIf function for further evaluation. Here's a couple of other weird situations from the .NET blog that this could give rise to (IIf in VB.NET has the same problems, 'cause its actually still the VB IIf from the Microsoft.VisualBasic namspace):

Dim info As String
info = IIf(foo Is Nothing, GetDefaults(), GetObjInfo(foo))

In VB/VB.Net, both functions GetDefaults() and GetObjInfo(foo) get called!

or this one:

Dim info As String = IIf(foo Is Nothing, "EMPTY", foo.data)

Before even calling IIf, the VB.NET compiler emits IL to:

- Evaluate the "foo is Nothing" conditional
- Load the string "EMPTY"
- Load the value of foo.data (but how can you load the value of foo.data
                              if foo is null?). You get a NullReferenceException.

Here's how C# resolves the problem. The C# conditional operator (indicated by a '?') test for division by zero correctly. If x doesn't equal 0, the division occurs, else 1 is returned. When the C# compiler encounters the conditional operator, it emits IL very similar to an if/then/else statement (which is really the preferred method for handling this test in VB too).

// cs_divisionby0_test.cs
using System;
class Test
{
   public static double sinc(double x)
   {
      return x != 0.0 ? Math.Sin(x)/x : 1.0;
   }

   public static void Main()
   {
      Console.WriteLine(sinc(0.2));
      Console.WriteLine(sinc(0.1));
      Console.WriteLine(sinc(0.0));
   }
}

Output
0.993346653975306
0.998334166468282
1

No wonder Microsoft has stated that it intends to support VBA for one more release of Office. I still have no idea why IIf isn't blowing up in the SQL case.

Bob