Iif, If Then Else, and Select Case in MS Access

This week, we go back to our MS Access playlist in order to find out how to use “If Then Else” and “Select Case” statements in our queries and code. Also, we’ll cover a handy function called “iif”, that can be used in both expressions AND VBA code to handle simple “if then else” scenarios. Get ready to ask the question, “what if?”
Related Videos:
Getting Started with MS Access Visual Basic for Applications - VBA Code-Behind and Modules
• Getting Started with M...
VBA MsgBox - How to use message boxes in MS Access
• VBA MsgBox - How to us...
How to Use a Do Until Loop in MS Access VBA
• How to Use a Do Until ...
How to Use Iif in Microsoft Access
• How to Use Iif in Micr...
How to Use the Replace Function in Microsoft Access
• How to Use the Replace...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
Iif, If Then Else, and Select Case in MS Access
You are watching this video now!
How to Use DLookup in Microsoft Access
• How to Use DLookup in ...
How to Create and Configure a Custom Pop-Up Form in MS Access
• How to Create and Conf...
Follow us on social media:
/ mackenziedat. .
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
For developers looking for a new role, check out:
www.toptal.com...
Want my team to do a project for you? Let's get to it!
system.billzon...
if then else statement
ms access
select case sql
ms access tutorial
ms access database
if then else in access query
if then else in access
select case in access vba
ms access if then else
vba if then else
vba select case example
vba iif
vba iif vs if
access iif function
access iif expression
iif in access query expression
sean mackenzie

[@jimmylandryramiandrison2450]

Please. How use "or" in VBA? Thank's

[@seanmackenziedataengineering]

You can do something like: If MyVariable = "ABC" And ReportDate = #1/1/2024# Then MsgBox "Some notification.." 'Do some more stuff End If or something like: If (MyVariable = "ABC" Or MyVariable = "DEF") And ReportDate = #1/1/2024# Then 'Do stuff End If or something like: If MyVariable = "ABC" Or MyVariable = "DEF" Then 'Do stuff End If

[@Pro-Tips-Tricks]

what if i need 3 or 4 results in applying iif function in design view(Example good="ok", very good="excelent",bad="try again",very bad="error"

[@seanmackenziedataengineering]

Great question - for this you need to use a nested iif statement. ie. iif("good", "ok", iif("very good", "excellent", iif("bad", "try again", "error"))) looks terrible, but it works in query design!

[@zalandafghan9647]

Whats wrong with this code plz... IIF(dcount("[absentid]","absentq","[paid]=false")>1, [txtabsence] & " days",[txtabsence] & "day") . . . . . it gives the result as #type! ..... Please help thanks

[@seanmackenziedataengineering]

Try "[paid]=0" or "Nz([paid], 0) = 0"

[@zalandafghan9647]

@@seanmackenziedataengineering tried that already.... Even tried Switch() function but no luck. If just do [field]>1then it works but with dcount it doesn't...

[@macba2000id]

Check the data type first from the table

[@teohaddad977]

What if you want to use show case and include another column?

[@seanmackenziedataengineering]

You can certainly do that; if I understood the question, you can indeed put a Select Case inside of a Select Case in order to handle multiple conditions.

[@nemo9396]

How would you filter records to display only one instance of the Visitor based on a priority list, say: If Angry is present, list only the Angry; else list Nervous; otherwise list Sad, etc.

[@seanmackenziedataengineering]

Great question! You could make a query of distinct visitors first. Then make another query on that one with a function. ie. If angry then show that else if nervous show that else show sad. You could use DLookup to check inside each of the nested statements. If DLookup returns null then move to the next step etc

[@macba2000id]

I love your t shirt anyway..send me one

[@seanmackenziedataengineering]

It is actually a set that comes with a baby onesie that has Ctrl+v on it! We had a baby around then. Good for laughs!

[@neophytosioannou7552]

Thank you for sharing your knowledge. I am new into access and trying to do everything by myself. My question it may be stupid but I dont know how to do it. If you can please help? I have some numbers that I have to Score with a Rank of 5 levels (Poor, Avg, Good, Very Good and Excellent). All those those numbers scoring depends on the gender and age. Any suggestions please

[@seanmackenziedataengineering]

Great question! Yes you can. In your Select Case statement, you can use And.. Case "Male" and Age < 10 .. assign a rank Case "Male" and Age < 15 .. assign a rank etc.

[@neophytosioannou7552]

@@seanmackenziedataengineering Thank you for being so kind and answering and for your help. Really appreciate that!!! I will try to make it happen.

[@neophytosioannou7552]

@@seanmackenziedataengineering Hi Sean. I tried everything you are suggesting but obviously I am doing something wrong. The Select Case statement looks like this Function AnP(RelMaxPower) Select Case (RelMaxPower) And (Age) And (strgender) Case Is < 6 And Age < 14 And Male AnP = "poor" Case Is < 6.65 And Age < 14 And Male AnP = "Average" Case Is < 7.33 And Age < 14 And Male AnP = "Good" Case Is < 7.99 And Age < 14 And Male AnP = "Very Good" Case Is >= 8 And Age < 14 And Male AnP = "Excellent" Case Else AnP = "Other" End Select End Function When I Run the function into a query I am receiving everything as "Excellent" but for sure this is not the case. Any suggestions please?

[@seanmackenziedataengineering]

@@neophytosioannou7552 You are very close. For multiple conditions, you can try something like: Function AnP(RelMaxPower, Age, strgender) Select Case True Case (RelMaxPower < 6 And Age < 14 And strgender = "Male") AnP = "poor" Case (RelMaxPower < 6.65 And Age < 14 And strgender = "Male") AnP = "Average" Case (RelMaxPower < 7.33 And Age < 14 And strgender = "Male") AnP = "Good" Case (RelMaxPower < 7.99 And Age < 14 And strgender = "Male") AnP = "Very Good" Case (RelMaxPower >= 8 And Age < 14 And strgender = "Male") AnP = "Excellent" Case Else AnP = "Other" End Select End Function I used it and it works great! Good luck!

[@Eliza-f3c]

This was very informative. Thank you! I do have one question, though. I need to use the results of the select case in my table instead of in a query. Can I do that? For example, I'm selling products, and different platforms charge different costs to print and ship the product. I want to calculate my net profit. So, I did a select case for Platform and CostFee. If it sells on Amazon, their fee is $4.50. If it sells on Shopify, their fee is $5.00, etc. That worked as you showed, but I need the results to appear in a field, so I can have another field that calculates the retail price minus the CostFee, so it will show net profit. Does that make sense? Is that possible? Thanks so much for taking the time to make such wonderful and helpful videos!

[@seanmackenziedataengineering]

Actually, you can use the Select Case in a function, then plug your function into a query to get what you're looking for: kzbin.info/www/bejne/n2q6YnSYoMxomdU Check it out!

[@Eliza-f3c]

@@seanmackenziedataengineering Thanks

[@teribridges4598]

Is it possible to Assign a percentage to a field selection? Let's say the user selects option a In the Task field, I want to have the status field to reflect 25% if they select B I want the status field to reflect 50% complete. I need the status field to be a number percent for reporting.

[@seanmackenziedataengineering]

@Teri good question! You can certainly output a percentage into a field, instead of a text output like we did here. For numbers, you don't need the quotes. For example you would say: MyFunction = 0.25 instead of: IsAngry = "He's angry." as we did above. If you have multiple percentages, like a = 0.25, b = 0.5, c = 0.25, then you will want to use Select Case as I show in the second part of the video.

[@joeerickson1967]

I don't understand how to connect the module to the query? I wrote a Select Case statement in a new module, but how do I use it in a query? I'm trying to create a "Fullname" field using Firstname, lastname, Title, Title2 fields. My report requirements change the order of firstname and lastname based on title.

[@seanmackenziedataengineering]

Joe Erickson, you can make a new field called FullName: MyFunction([firstname],[lastname],[title],[title2]) You’ll type that in a new field in your query, in the query grid. Make sure your module function takes all 4 fields in order, in the arguments in the first line. Let me know how it goes!