[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


97 Access multiple IIF query

Posted on 2011-10-06
Medium Priority
Last Modified: 2012-08-14
I use access 97.

I have 2 tables linked together. Tblpatients and tblevent. I need a IF-then query.
Here is the situation. If you are a medicare part A patient after 10/1/2011 and you are treated with the codes 97150 or 92508 your minutes of treatment are divided by the number of people in the group.
Here is my statement without the “after 10/1/2011”
Mins: IIf([tblpatients]![PtInsur]="medicare Part a",IIf([tblEvent]![CPT Code]="97150" And "92508",[tblEvent]![U-value]/[tblEvent]![Group],[tblEvent]![U-value]),[tblEvent]![U-value])
This statement works, but I do not know how/where to add the [tblEvent]![DateofServ]>9/30/2011

Any help appreciated.

Question by:Tomcpt
  • 2
  • 2
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 600 total points
ID: 36924717
Access 97 = Dark ages  ;-)

How about:

Mins: IIf([tblpatients]![PtInsur]="medicare Part a" AND [tblEvent]![CPT Code] IN ("97150", "92508") AND [tblEvent]![DateofServ]>#9/30/2011# ,[tblEvent]![U-value]/[tblEvent]![Group],[tblEvent]![U-value]),[tblEvent]![U-value])
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36924806
try this

Mins: IIf([tblpatients]![PtInsur]="medicare Part a" AND [tblEvent]![CPT Code] IN ("97150", "92508") AND [tblEvent]![DateofServ]>#9/30/2011# ,[tblEvent]![U-value]/[tblEvent]![Group]),[tblEvent]![U-value])
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1400 total points
ID: 36924820
too many parentheses...

try this

Mins: IIf([tblpatients]![PtInsur]="medicare Part a" AND [tblEvent]![CPT Code] IN ("97150", "92508") AND [tblEvent]![DateofServ]>#9/30/2011# ,[tblEvent]![U-value]/[tblEvent]![Group],[tblEvent]![U-value])
LVL 49

Expert Comment

by:Dale Fye
ID: 36924965
Good catch cappy.  I forgot to delete the last part of the original nested if.

Author Closing Comment

ID: 36949727
Fyed, thanks for the quick response. I know acc 97 old but it still works. I placed the # on both sides of my dates in my formula and it worked. When I used yours I received and error.

Cappy I used your formula as it worked great and is best practice, therefore I awarded you the majority of the points.

Thanks you both as this board is a life saver.


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question