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

[@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!

[@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

[@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

[@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.

[@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

[@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

[@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!

[@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.

[@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!