Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 31st, 2003, 08:49 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default IIF Statement Question

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?
 
Old October 31st, 2003, 09:24 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by fastcorvette
SELECT *, IIF(PricePer > 0, (Cost/PricePer), 0) as MAmt FROM Table WHERE Num = @Num
Perhaps the addition of the IIF statement then makes Access mistakenly misinterpret the slash in (Cost/PricePer). How about being more explicit?
Code:
   SELECT *, IIF([Table].[PricePer] > 0, _
   [Table].[(Cost/PricePer)], 0) As MAmt FROM Table WHERE Num = @Num



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old October 31st, 2003, 10:50 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
 
Old October 31st, 2003, 12:16 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by fastcorvette
 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
 
Old October 31st, 2003, 12:29 PM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old October 31st, 2003, 12:34 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 31st, 2003, 12:53 PM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old October 31st, 2003, 05:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



 
Old October 31st, 2003, 11:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I make this a IIF Statement Corey Access VBA 1 November 3rd, 2006 08:40 AM
Iif statement in Access rangeview Access 7 March 28th, 2006 01:14 PM
IIF Statement is a Date field Corey Access 17 November 12th, 2005 06:50 PM
IIF Statement golden Access 2 August 3rd, 2004 01:32 AM
IIF Statement golden Access 3 July 12th, 2004 07:49 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.