IIf function query not showing complete results

[Top Tuna]

I am currently trying to update a medication database I have written which calculates the number of tablets and prescriptions I have remaining on any given day. I have a start date (that the medication started) and a current date. Obviously the difference between the two is the number of days that have passed reducing my supply by the number of days by the dose per day. I am trying to introduce a suspension period that sometimes occurs, i.e. a date that the medication is stopped and then possibly resumed. I have constructed an IIf function in my query to make the calculation. Everything works except if there is no value in [ScriptSuspended], I don’t get a value in the [DaysSupplied] field. It does appears if there is a date in [ScriptSuspended], or there is a value in [DaysSuspended]. I don’t know if this makes sense, or indeed there may be an easier way. But I am stumped. If anyone could help that would be amazing.
This is my query: DaysSupplied: IIf([DaysSuspended] Is Null,[ScriptSuspended]-[StartDate],IIf([DaysSuspended] Is Not Null,(Now()-[StartDate]-[DaysSuspended]),IIf([ScriptSuspended] Is Not Null,[ScriptSuspended]-[StartDate],IIf([ScriptSuspended] Is Null,Now()-[StartDate],Now()-[StartDate]))))

[Micron]

Nested IIf's are often ugly. 4 of them are down right hideous and I'm not even going to try to figure that one out! The obvious issue is that you can't subtract from Null or an empty string. The not so obvious is why you've taken this approach but I wonder if it's because the tables are not normalized. I'm not seeing why you need that expression, but perhaps you could post some data from your table(s) to make it clearer. This sort of exercise looks like inventory tracking where (often) one field holds the transactions. If you start with a count of 30 and there are 27 date entries of 1 dose per, then you total the 30 + (27 of -1) which means you have 3 remaining. If you purchase 30 more, you have a dated transaction of 30 for a total of 33.

If you're going to suspend a script, why not simplify the process by not making records for that period? I don't see why it has to be factored into a calculation.

[Top Tuna]

Thanks for your response Micron.
My comprehension of Access is fairly limited so my structure from the start seems to have lead me down the path of needing this type of calculation.
I don't quite understand how I would go about "simplifying the process by not making records for that period". I think my query design might be a bit over complicated.
My query structure without any suspended dates is simply reducing the quantity remaining by subtracting today's date from the start date mutiplied by the daily dose.
All I am attempting to do is add in a calculation that would take care of any period that the medication is suspended. Either from the start to the suspended date, of from the start to now minus the period of suspension.

[davegri]

Looking at the IIF maze, I think I see this:

The logic can never progress beyond the first two IIFs
If daysSuspended is null, the logic stops with the first IIF.

If the first iif is false, the second has to be true and the logic stops there.

[Top Tuna]

Thanks for your help davegri.I really appreciate your response.

[Micron]

It's not clear to me what the process is. I get that you are using date spans x dosage to calculate remaining. Question is, how/when are you adding records - or are you even doing that? If you're not, perhaps Excel is a better choice as it's better with working with numbers but it's not a database. On the other hand, you might be adding records on some other basis than daily - perhaps weekly and that is why you're using date spans. Some things you could do to help us help you:
- post a pic of your table relationships if you have created them
- explain the process in detail (how you use the db); we're probably all presuming that you are using a form or forms for this for example.
- copy, compact, zip and post your db copy. If you need to, alter data for privacy (see https://www.accessforums.net/showthread.php?t=77482)

Trying to use expressions like the one you posted is a sign that the tables might not be properly structured and that is worth figuring out first.

[CJ_London]

Would help to show your table design and relationships plus some example data and the outcome required for that sample data

[Top Tuna]

Micron, Thanks for your interest in my problem. I have attached my zipped database, warts and all for you to have a look at. This database works but I want add the ability to suspend medication for a given period. That is the dilemma.
I trust this will provide you with a better understanding of where I am with this.

[Micron]

Will take some time to review (soon but it's after 11 PM here) and formulate questions. A quick review suggests the tables are not quite right but I lack sufficient understanding of the process to say by how much. At first glance:
- client id doesn't belong in tblPrescriptions. More than 1 person could use the same medication thus you'd repeat too much data (name & alternate name for sure). Seems to me you need tblMeds and tblPrescriptions to fix that.

- I'm used to seeing prescription numbers on any vial I get so perhaps it's a vital but missing field

- If a script of 30 @ 1/day was issued 08/12/20 isn't the date for next repeat around 09/11/20? You have NewSupplyDate of 04/06/21. OK, perhaps db isn't to be used as I would design it according to rules I'd be subjected to (I can't get 6 months supply all at once). That's where understanding comes in.
Regardless, the new date seems to be a field that shouldn't be there. Such calculations should be handled by forms and not be stored.

- I don't get the notion of a 30 count script @ 1/day yet a monthly count of 28. Again, could be a lack of understanding. Could also be another calculation.

- ForwardPurchaseID in tblPrescriptions is likely incorrect, same reason as first point re clientID

The foregoing probably isn't making a whole lot of sense if you don't understand db normalization concepts. I suggest that you either study this with a view to your process and proceed from there, or advise if you simply wish to stay the course. I don't have an issue either way but can tell you that without a proper foundation OP's will often be coming here for help. Sometimes there might not be a fix. Sometimes they might not get one because they ignored advice to fix the foundation. Here's some normalization stuff if you care to review it:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

If you decide to study normalization, perhaps take another crack at design and either post questions or another db example. Also, might help to think of entity/relationships like Nouns and Adjectives - nouns are tables, adjectives are fields. That should suggest that Client is not an adjective of a prescription thus it doesn't belong there. The harder part is correctly deciding WHERE it does belong.

In case you're interested in other newbie traps:

Naming conventions
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[Top Tuna]

Thank you Micron for your suggestions. I think I will go away and study your comments, suggestions and references. I will redesign the entire db to achieve my desired outcome.

[Top Tuna]

Thank you Micron for your suggestions. I think I will go away and study your comments, suggestions and references. I will redesign the entire db to achieve my desired outcome.

[Micron]

OK, good luck. See if this quick guess is along the lines of what you decide to design. Incomplete no doubt and perhaps not suitable for your purpose, which isn't entirely clear yet. e.g. you have a field for count per script but is that 30 that is issued at a time, or 6 repeatsx30 = 180 script count? Doesn't matter, just explaining how things are open to interpretation and I've taken lots of liberty there with the following:

tblPatient
PatientID	autonumber PK
Lname	text, 25
Fname	text,25
DOB	date
Address	text, add fields as desired (e.g. Unit/Apt, Postal)
Phone	text, 10?
Cell	text, 10?
etc

tblMeds	data only about meds
MedID	autonumber PK
MedName	text, 25?
GenericName	text, 25?
etc	manufacturer?

tblScripts	what I get from doc & take to pharmacy
ScriptID	autonumber PK
ScriptNum	text if leading zeros req'd (num on bottle label)
MedID_fk	long, autonum from meds
PatienIDfk	long, autonum from Patients
IssueDate	date
Qty	integer
Repeats	integer
Dose	integer? e.g. 2
Frequency	text? e.g."every 4 hours"
	or dose is 1 field with "take 2 every 4 hours"
etc

[Top Tuna]

Thanks again Micron. I will give it a go. Cheers.

[CJ_London]

Just to add a comment from my work in the medical world which may or may not be relevant

We split medications into two tables one for the type e.g. aspirin/ramipril and a second with a fk to the type and another field to include size e.g. 50mg. We also had manufacturers and brand tables which linked back to this second table

[Top Tuna]

Thanks Ajax. That's a good idea. I will incorporate that suggestion.