Calculating dates

cunningham93

Registered User.
Local time
Today, 03:56
Joined
Mar 17, 2014
Messages
13
Hello,
I am tracking the clean time of clients who are coming out of inpatient treatment. I am wondering how to go about calculating this in an ongoing manner in my query. If a client test positive for a substance on a given date my query will calculate the number of days from the discharge date to the positive test date (basically, I’m only tracking positive test result and if their negative, nothing is entered). What I’d like to do is to calculate in an ongoing manner the number of days clean since being discharge from treatment to today’s date unless there is a positive test result in which case it the query would calculate the number of days the person had clean between the two dates.
Thank you in advance for your help and I apologize if I’m not being clear.
 
What's your table structure? It would be different based on the data being in one table or two (like clients and "incidents").
 
I do have a few different tables. First, i have a main table which is information that does not change i.e. name, DOB, Education, Judge, Probation Officer assigned, etc.. This is connected to an Inpatient Table which has treatment information including date entered and discharged. This is conntected to an Outpatient Table which has outpatient relevant information such has outpatient treatment provider, date entered and discharged, discharge type, etc.. This is connected to a drug test table which simply is drug test date and what they tested positive for.
These table are all connected to each other with a one to many relationship as clients will often have more than one treatment attempt. My cuurent query works brings in all the necessary information and I have set up calculation fields to track the number of days a person is in treatment, number of days in jail, etc.. The query will only give me the number of days clean once their is a positive drug test as I cannot figure out how to calculate this in an ongoing manner using todays date.
Thanks for your help!
 
What's the SQL of that almost-working query? If it's what I suspect, you'd edit the join to get all fields from the client table so that it pulls all records from there, whether there's a record in the incident table. Then in your calculation where you calculate the days, you'd use the Nz() function like:

DateDiff("d", Nz(PostiveTestDate, Date()), TreatmentDate)
 
Here is the SQL:
SELECT MasterTable.LastName, MasterTable.FirstName, MasterTable.Sex, ([InpatientTable.DateEntered]-[DOB])/365 AS Age, MasterTable.Judge, MasterTable.Education, InpatientTable.DOC, ([CustodyDateDischarged]-[CustodyDateEntered]) AS JailTime, InpatientTable.TreatmentCenter, InpatientTable.TreatmentType, InpatientTable.DischargedType, ([InpatientTable.DateDischarged]-[InpatientTable.DateEntered]) AS TreatmentDays, OutpatientTable.TreatmentCeter, OutpatientTable.TreatmentType, OutpatientTable.DateEntered, OutpatientTable.DateDischarged, OutpatientTable.DischargeType, OutpatientTable.[AA/NAMeetings], DrugTestsTable.DrugTestDate, DrugTestsTable.DrugTestResult, DrugTestsTable.Drug, InpatientTable.PTVP, InpatientTable.DateDischarged, ([DrugTestDate]-[InpatientTable.DateDischarged]) AS DaysClean
FROM (MasterTable LEFT JOIN (InpatientTable LEFT JOIN OutpatientTable ON InpatientTable.Outpatient_ID = OutpatientTable.ID) ON MasterTable.ID = InpatientTable.ID) LEFT JOIN DrugTestsTable ON OutpatientTable.Drug_ID = DrugTestsTable.ID;
You are correct about having to adjust the joins in the query so that it brings in all the information necessary. This query does work correctly excluding the calculation of days cleans. Currently it only calculates this once there is a date in the drug test table. If there is nothing, the field in the query remains blank. My current expression is DaysClean: ([DrugTestDate]-[InpatientTable.DateDischarged]) Thank you!
 
Does this work?

([DrugTestDate]-Nz([InpatientTable.DateDischarged], Date()))

By the way, I'm a little surprised that syntax works. I'd expect the table and field to be bracketed separately:

[InpatientTable].[DateDischarged]
 
The expression you sent does the same as I wrote. Where there is not a drug test date, the field remains blank and where there is a drug test date, the field gives the correct number of days clean prior to the positive date.
 
Shot in the dark, but try without the brackets:

([DrugTestDate]-Nz(InpatientTable.DateDischarged, Date()))

If not, can you post the db here, or a representative sample?
 
No names?!? What fun is that? :p

I think that's working fine, I think the problem is most records don't have a value for the other part of the equation, DrugTestDate.
 
That is correct, and the reason is that those clients have not had a positive test yet. When I created the Drug test table I was think that I would record all test results and after more thinking, I thought it would be easier to just record the positive ones which of course would indicate a relapse. I was also thinking that this would make things more simple in tracking clean time of course, it didn't work out that way. I can change anything as I have just started this and would appreciate any suggestions you might have. Thank you again for your time.
 
But in order to calculate an elapsed time, you need two dates. Is there a date that can be used if the test date is Null? Offhand I don't see a date field that's consistently filled out.
 
I think I understand and no there is not a date field that is consistantly filled out with todays date. If I were to enter all drug tests and noted both positive and negative, could i use the negative test date as the date needed to get an accurate count of days? If so, how would i go about continuing the count with each following test, next week, the week after and so forth?
Would it be possible or easier to use an IIF statement to the effect that if the date field is null, return a note of "clean"? I know that this would not give me a count of days clean but I could always make a table with this information at the time of a report and put in a date to get a count at that time. What are your thoughts? Thank again for all your time!
 
I wasn't thinking today's date so much as a default start date if they had no positive test. For instance if the master table had a date field that was the date they started with you or something like that.

You could certainly wrap the DateDiff() in an IIf() that tested the first date and put "clean" or 0 or whatever you want.
 
Thanks for your input. I given some thought to creating a start date on the master table as you suggested and the only challenge I see with that is clients often have multiple treatment episodes and that date would have to change when this occurs. At this point, I think it would be best to continue as it is and only add an IIF statement to the calculation so that if the date in question is null it would return a note like "Is Clean". I think could use this same calculation in other areas in the event the client is still in treatment and has not yet been discharged. I've not written anything like this in the past and whatever instructions you might have on this would be appreciated. Thanks again for your assistance.
 
Along the lines of:

DaysClean: IIf(IsNull([DrugTestDate]),"clean",[DrugTestDate]-nz(InpatientTable.DateDischarged, date()))

But note using text like "clean" instead of a number like 0 may interfere with calculations down the line.
 

Users who are viewing this thread

Back
Top Bottom