Friday, May 07, 2004

 

Functions in Microsoft Jet Queries

The IIf (Immediate If) Function

The IIf function takes three arguments, referred to in the help files as “expr”, “truepart”, and “falsepart”. The function returns the value of the “truepart” argument if the “expr” (expression) argument evaluates to True. It returns the value of the “falsepart” argument if the “expr” argument evaluates to False.

For example, suppose we have a field (TestText in this example) in a table (tblTest in this example) that may contain Null values. What we’d like to display to the user is the value of the field if it is not Null, or the text “N/A” if the value of the field is Null. The following query will achieve this goal:

SELECT IIf([TestText] Is Null,"N/A",[TestText]) AS TestIIf FROM tblTest;

Microsoft Access has an NZ function that can achieve the same goal more simply:

SELECT NZ([TestText],"N/A") AS TestNZ FROM tblTest;

However, the NZ function is implemented in the Microsoft Access object library, not in the VBA expression service, which means that it can only be used within the Microsoft Access environment. The IIf function is implemented in the VBA expression service, and the Jet database engine “knows” how to call functions in the VBA expression service even when the query is executed outside of the Microsoft Access environment.

In addition, the NZ function is a specialised function, dedicated to the purpose of handling Null values. The IIf function is a much more flexible general-purpose function that can handle a much wider range of conditions. For example, the following query will display the letter “A” if the field “TestInteger” contains a value less than or equal to 3, otherwise it will display the letter “B” (the kind of thing you might want to do when grading test scores, for example):

SELECT IIf([TestInteger]<=3,"A","B") AS TestIIf FROM tblTest;

Interestingly enough, the query above displays the letter “B” if the “TestInteger” field contains a Null value. Under most circumstances, “Nulls propagate”, in other words, if any part of an expression is Null, the result of the expression will be Null. This is because Null represents an unknown value, and if any of the elements of an expression are unknown, then the result must also be unknown. In the example above, if the value of the field “TestInteger” is unknown, how can we determine whether the value is less than or equal to any other value? The answer is unknown, in other words, Null. The IIf function, however, can only return one of two values, the values of the “truepart” and “falsepart” arguments. It doesn’t have a “neither of the above” option! It has to evaluate the “expr” argument as either True or False, it does not have the “option” of deciding that the expression should be evaluated as Null.

Handling Null values provides us with one example of why we might use “nested” IIf functions – one or more IIf functions within another. Building on our previous example, suppose we want to distinguish between values that are less than or equal to 3, values that are greater than 3, and Null (unknown) values? The following query, using nested IIf functions, will do that job for us:

SELECT IIf([TestInteger] Is Null,"N/A",IIf([TestInteger]<=3,"A","B")) AS TestIIf FROM tblTest;

If the field “TestInteger” contains a Null value, this expression will display the text “N/A”. If the field “TestInteger” does not contain a Null value, the expression will display the result of the second, “nested” IIf function, which will be either “A” or “B”.

You can build quite complex and powerful expressions using nested IIf functions. They can, however, very quickly become difficult to read, and therefore difficult to modify when the need arises. You may find that you can achieve the same objective in a more easily maintained way using one of the other conditional functions, Choose or Switch. (I covered the Choose function in an earlier post, and I’ll cover the Switch function in a forthcoming post.)

There’s an interesting difference between the behaviour of the IIf function in VBA code and the behaviour of the same function in Jet queries. In VBA code, while the IIf function will return only one of the “truepart” or “falsepart” arguments, it will attempt to evaluate both of them. This can have “interesting” side effects. For example, suppose you attempt, in VBA code, to use the IIf function to avoid a potential divide-by-zero error:

Public Function TestIIf(TestInteger As Integer, TestLong As Long)

    TestIIf = IIf(TestInteger = 0, "N/A", TestLong / TestInteger)
    
End Function

This won’t work. If you call this function with a value of 0 in the TestInteger argument, you’ll still get a divide-by-zero error. This is because, even though the function is not going to return the result of the “falsepart” argument (TestLong / TestInteger) it still attempts to evaluate that expression, thereby raising the very error you were trying to avoid. (You can avoid this in VBA code by using If … Then … Else … End If instead of IIf).

In a Jet query, the “falsepart” argument is not evaluated if the “expr” argument (the condition to be tested) evaluates to True. The IIf function returns the value of the “truepart” argument and does not attempt to evaluate the “falsepart” argument in those circumstances. So the same expression can be used successfully to avoid the divide-by-zero error in a query:

SELECT IIf([TestInteger]=0,"N/A",[TestLong]/[TestInteger]) AS TestIIf FROM tblTest;

This works, successfully avoiding divide-by-zero errors, both within the Microsoft Access environment and when executed by our C# “JetTest” utility (see previous posts).


Send feedback on this post to (change the obvious):

Brendan Reynolds
bwnnfb at hotmail dot com

(For the insatiably curious, "bwnnfb" is an acronym for "Blog With No Name Feed-Back")

Copyright © 2004 Brendan Reynolds. All rights reserved.


Comments:
Free Casinos tyuueooru
http://stonewalljacksoncarnival.org/ - Play Casino
No matter how far your casino is located With online casino, you don?t have to worry about the location of your casino because the only thing that creates between you and your casino experience is the internet, so all you need to do is getting connected to the Internet and you can enjoy the gambling without having to spend your time visiting the local casinos out there.
[url=http://stonewalljacksoncarnival.org/]Free Casino Gambling[/url]
More casino options The number of online casino is far higher than bricks and mortars based casino all across the world.
Download Online Casino
Play wherever and whenever you want The best thing about online casino is that you don?t have to visit your local casino in order to meet your gambling desire.
 
This information is very useful. thank you for sharing. and I will also share information about health through the website

Obat Pengapuran Otak pada Anak
Cara Mengobati Cacingan
Cara Mengobati Pengapuran Tulang
Obat Benjolan di Belakang Telinga
Cara Cepat Mengobati Amandel
Obat Tradisional Radang Tenggorokan


 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Obat abses hati
Obat liver bengkak ampuh
Agen QnC Jelly Gamat banjarmasin utara

 
may be useful for all, helpful article once and pardon me permission to share also here :

Cara mengatasi sesak nafas
Obat tradisional hepatitis b
Cara menyembuhkan flek paru paru
 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Bahaya penyakit amandel
Manfaat pisang ambon untuk kesehatan
Cara mengatasi daya ingat menurun
 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Obat tumor kista ovarium
Obat pembengkakan limpa
Cara mengatasi lambung kronis
Obat tumor rahang
Obat gagal hati
Obat ginjal mengecil
Cara menghilangkan jerawat
Cara menghilangkan benjolan di leher

 
This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Cara Mengatasi Diare
Obat Sariawan Alami
Obat Penghancur Batu ginjal
Cara Mudah Mengobati Diabetes
Cara Menyembuhkan Mata Minus
Cara Mengobati Gusi Bengkak
 
may be useful for all, helpful article once and pardon me permission to share also here :

Cara menyembuhkan osteoarthritis
Obat epilepsi
Obat gabagen
Obat tersiram air panas
Obat hepatitis b
Obat keloid bekas operasi
Cara menghentikan pendarahan lambung
 
If you have a history of illness that is difficult to recover, maybe our next article will help you to recover

Obat Penghilang Benjolan di Leher
Cara Melacarkan Haid
Cara Mengatasi Pengapuran Tulang
Obat Keputihan alami
Walatra Propolis Brazil

 
thank has been sharing this information. Do not forget to visit our website to share information and knowledge about health.
Obat Vertigo Di Apotik
cara mengobati nyeri dada
Pengobatan Batu Empedu Dan Cara Mengatasinya
 
This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Penyebab Sering Nyeri Ulu Hati
Obat Penyakit Crohn
Obat Migrain Tradisional
Solusi untuk Menanggulangi Osteoporosis
Cara Mengobati Abses Payudara Tanpa Operasi
 
This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Obat Penyakit Susah Kencing
Cara Mengobati Gondok Beracun
Cara Mengobati Gabagen
Cara Mudah Mengobati Diabetes
Cara Menyembuhkan Mata Minus
Cara Mengobati Gusi Bengkak
 
thank you very useful information admin, and pardon me permission to share articles here may help :

Cara meredakan jantung berdebar
Cara mengatasi radang usus
Cara menghilangkan biduran
Cara untuk sembuhkan miom
Cara meringankan gejala eksim
Cara meredakan sakit rematik
Cara menangani cacar air
 
Thanks for the information, this is very useful. Allow me to share a health article here, which gods are beneficial to us. Thank you :)

Obat Telinga Bernanah paling Ampuh
Cara Memesan QnC Jelly Gamat
Cara Mengobati kista Bartholin tanpa Operasi
Biaya dan Efek Samping Operasi Kista Bartholin
Obat ISPA paling Ampuh
Cara Mengobati Kencing yang Tersendat

 
may be useful for all, helpful article once and pardon me permission to share also here :

Cara mengobati bronkitis
Pengobatan dispepsia

 
articles as a very interesting and rewarding once, and pardon me permission to share articles here hopefully helpful and useful :

Obat benjolan di ketiak
Obat infeksi saluran pernafasan
Cara menyembuhkan tipes

 
thank you very useful information admin, and pardon me permission to share articles here may help :

Suplemen untuk mata katarak
Pengobatan penyakit kuning
Pengobatan epilepsi
Pengobatan asam urat
Pengobatan bronkitis kronis
Cara mengatasi perih ulu hati
Obat herbal tbc ampuh
 
thank you very useful information admin, and pardon me permission to share articles here may help :

Obat batuk berdarah ampuh
Obat step
Obat radang telinga ampuh
Obat gerd tradisional
Obat bilirubin tinggi
Cara menyembuhkan syaraf kejepit
Obat untuk melancarkan BAB
 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Obat paru paru basah ampuh
Obat disentri ampuh
Obat kencing batu ampuh
Obat mata kering ampuh
Obat penghancur batu ginjal
Obat kesrek ampuh

 
thank you very useful information admin, and pardon me permission to share articles here may help :

Obat lambung bengkak
Obat jantung bengkak ampuh
Obat limpa bengkak ampuh
Cara mengatasi keputihan dengan daun sirih
Obat benjolan di kepala ampuh
Obat penambah berat badan
Obat kista coklat ampuh
 
This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Pengobatan Scabies secara Alami
Cara Menyembuhkan Batuk Berkepanjangan
Cara Mengobati Pengapuran Tulang
Obat Pengapuran Otak pada Anak
Cara Mengatasi Mata Bengkak
Obat Herpes Zoster

 
Thank you very useful information admin, and pardon me permission to share articles here may help :

Ardian herbal
Cara menyembuhkan kencing berdarah
Obat jantung bocor alami tanpa operasi


 
may be useful for all, helpful article once and pardon me permission to share also here :

Cara menyembuhkan pembengkakan hati
Cara menyembuhkan tukak lambung
Obat limpa bengkak
Obat lambung bengkak
 
If you have a history of illness that is difficult to recover, maybe our next article will help you to recover

Walatra Berry Jus
Walatra sehat mata
Qnc jelly gamat
Obat Kutil Kelamin Pada Pria & Wanita
Obat Infeksi Jamur Di Selangkangan
Obat Radang Lambung
Pengobatan Flek Paru-paru
Cara menghilangkan jamur pada lidah bayi
 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Nuyafada herbal
Obat tradisional kanker lambung
Cara mengatasi keputihan berbau
Obat anemia ampuh
Obat kanker ovarium
Obat radang usus ampuh
Cara menyembuhkan lambung bocor
 
This article is interesting and useful. Thank you for sharing. And let me share an article about health that God willing will be very useful. Thank you :)

Obat Alami Menurunkan tekanan Darah Tinggi
Obat Penyakit kulit Eksim
Cara Mengobati Kencing Tersendat
Obat Tradisional Telinga Berdengung
Walatra Gamat Emas Kapsul
Cara Mengobati Prurigo
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?