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
 
i read a lot of stuff and i found that the way of writing to clearifing that exactly want to say was very good so i am impressed and ilike to come again in future.
cara menyembuhkan iritasi mata
obat luka gangren
obat benjolan di tangan
obat tumor
obat ginjal bocor
 
I have read through other blogs, but they are cumbersome and confusing more than your post. I hope you continue to have such quality articles to share with everyone! I believe a lot of people will be like to read this article!
obat testis bengkak
reka nurhanisa
 
This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around

routine

risa herbal
penyebab sakit pinggang
obat penyakit mata glaukoma
obat gagal ginjal
obat penebalan dinding rahim
obat penghancur kanker
obat varikokel
 
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 Mengobati radang Usus
Cara Mengatasi sakit pundak dan Leher kaku
Cara Menghilangkan Benjolan di Bibir
Obat Nyeri haid
Tips Menghilangkan Wajah kusam
Cara Mengobati Bisul
 
the website you manage is very good and in my opinion this is the best wesbite
obat benjolan di selangkangan paha
 
given article is very helpful and very useful for my admin, and pardon me permission to share articles here hopefully helped :

Cara menyembuhkan radang hati
Cara mengobati infeksi lambung
Obat gudik ampuh
Cara menyembuhkan biduran
 
Hopefully, sustenance will be easy and simplified in all matters :-)
KHASIAT JELLY GAMAT GOLD-G
Obat Neuropati di apotek
Obat Sesak Nafas Alami
obat batu ginjal alami
obat syringoma
 
This article is very useful, thank you for sharing. And allow me to share articles too, it's about health and treatment. God willing it will be useful for us all. Thank you :)

Obat Radang Gusi (Gingivitis)
Obat Pereda Nyeri Dada Sebelah Kiri
Obat Untuk Miss V Gatal dan Perih/Nyeri
Pengobatan Gagal Ginjal Tanpa Cuci Darah
Cara Menyembuhkan Selesma(Flu) dengan Cepat
Cara Mengobati Luka Jahitan Bernanah

 
The post is written in very a good manner and it contains many useful information for me. Thank you very useful information admin, and pardon me permission to share articles here may help :

obat benjolan di bawah rahang
obat amandel bengkak
obat benjolan di ketiak
obat tumor ginjal
obat lambung bocor
obat borok di kepala
 
The post is written in very a good manner and it contains many useful information for me. Thank you very useful information admin, and pardon me permission to share articles here may help :

obat benjolan di bawah rahang
obat amandel bengkak
obat benjolan di ketiak
obat tumor ginjal
obat lambung bocor
obat borok di kepala
 
Thank you very useful information admin, and pardon me permission to share articles here may help :

Cara menyembuhkan kutil kelamin pada pria secara alami
Cara menyembuhkan radang amandel tanpa operasi
Cara menyembuhkan radang tenggorokan pada bayi
 

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 Sakit Dada
Obat Tumor Jinak diatas Bola Mata
Penyebab Benjolan dileher
Obat Penghilang Nyeri Pada Payudara
Pengobatan penyakit Meningioma
Obat Luka Bernanah bekas Caesar
 
Utilizing natural ingredients from nature, can create a wide variety of herbal remedies that are very beneficial for health with natural ingredients authenticity

Pengobatan Kelenjar Getah Bening
Obat Untuk Mengangkat Kanker Payudara Tanpa Operasi
Cara Menghilangkan Bopeng Bekas Jerawat
Pengobatan Liver Secara Alami
Cara Alami Mengobati Kanker Pankreas
Pengobatan Tipes Secara Herbal
 
I would like to thanks for sharing high value and informative article with us. And i hope you'll sharing more idea's and keep writing more like this one.

obat biduran
obat asam lambung
obat herpes
obat liver
obat keputihan gatal
obat ginjal rusak

 
I would like to thanks for sharing high value and informative article with us. And i hope you'll sharing more idea's and keep writing more like this one.

obat biduran
obat asam lambung
obat herpes
obat liver
obat keputihan gatal
obat ginjal rusak

 
Hello, we are from the health site admin, want to share our latest articles :

obat benjolan di pipi
obat radang lambung
obat benjolan di kelopak mata
obat batu empedu
obat kencing nanah
obat tipes
obat benjolan di belakang kepala
 
Thank you, for getting something new and saluting the originality of this story, hopefully useful, so that life is more useful and useful for others

Obat Gatal Pada Kemaluan Yang Terbukti Ampuh Dan Efektif
Cara Menurunkan Kolesterol Tinggi
Cara Mengobati Ambeien
Cara Mengobati Gatal-gatal Pada Kulit
Obat Amandel Paling Ampuh Dan Manjur
 
Thank you, for getting something new and saluting the originality of this story, hopefully useful, so that life is more useful and useful for others

Obat Gatal Pada Kemaluan Yang Terbukti Ampuh Dan Efektif
Cara Menurunkan Kolesterol Tinggi
Cara Mengobati Ambeien
Cara Mengobati Gatal-gatal Pada Kulit
Obat Amandel Paling Ampuh Dan Manjur
 
thanks you the article really helped me. I hope the articles that are made are useful for all of us.

Cara Mengobati Penyakit Aritmia
 
It's great that the article thank you for uploading, hopefully useful for writers and those who read

Cara Mengatasi Infeksi Saluran Kemih
Cara Alami Mengobati Thalasemia
Cara Alami Mengobati Polip Serviks
Obat Penyakit Kista Pada Wanita
 
Post a Comment

<< Home

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