Excel formula works... Syntax probs in Expression Builder

[Liz H]

I want to count patient days within a month. I have solution reached on 04/19/02 on this bulleting board, this is less cumbersome. Excel Formula: =IF(OR(C12>$AL$3,D12<$AL$2),"",IF(AND(C12>=$AL$2,D12<=$AL$3),D12-C12,IF(AND(C12>=$AL$2,D12>=$AL$3),$AL$3-C12,IF(AND(C12<$AL$2,D12>$AL$3),$AL$3-$AL$2+1,D12-$ AL$2+1)))) Note: Absolute cell references are First day and last day of month, other cell references are admit date and discharge date. I tried using the Expression builder and continually received syntax errors. Please help convert this to an access formula, this is what didn’t work in Access:
=Iif(Or( [Admit Date] >04/01/02, [DischargeDate] <04/30/02), " ", Iif(And( [Admit Date] >=04/01/02, [DischargeDate] <=04/30/02), [DischargeDate] - [Admit Date] , Iif(And( [Admit Date] >=04/01/02, [DischargeDate] >=04/30/02,04/30/02- [Admit Date] ,Iif(And( [Admit Date] <04/01/02, [DischargeDate] > 04/30/02).04/30/02-04/01/02+1, [DischargeDate] -04/01/02+1))))
Any help is greatly appreciated!

 

[Geoff Codd]

i think you need to put hashes # either side of dates

 

[Liz H]

Thanks! No doubt that had to be done, but it highlights my very first "or" telling me I've entered a common with out a prededing identifier or value.

 

[David R]

Try IIF([Admit Date] >04/01/02 OR [DischargeDate] <04/30/02),...

The syntax is different in Access.

 

[Liz H]

I tried that, now it seems it won't accept more than one Iif. My current looks like this:
IIf([Admit Date]>#04/01/2002# Or [DischargeDate]<#04/30/2002#,"0", Iif(( [Admit Date] >=#04/01/02#and[DischargeDate] <=#04/30/02#, [DischargeDate] - [Admit Date] ) ,Iif(( [Admit Date] >=#04/01/02#, [DischargeDate] >=#04/30/02#,#04/30/02#- [Admit Date] ,Iif( [Admit Date] <#04/01/02#and [DischargeDate] > #04/30/02#).#04/30/02#-#04/01/02#+#1#, [DischargeDate] -#04/01/02#+#1#))))

 

[David R]

I'm sorry, I don't understand what all of this is trying to do?

You want to count the number of days a patient is in your hospital for the course of a month? Are we talking about the potential for multiple visits in a month or only one? I know they can start or end before the month begins or ends, you seem to have covered that...

I just don't think the Excel code is going to translate very well to Access, because Access works so differently. What wasn't working about the Excel method?

 

[Liz H]

David,
I am counting the patient days within a month. Not the patient's total stay, thus the variables. Actually, the Excel formula works fine. I'm lazy and would rather just have a query and not export my data etc. I am in awe of the problem's resolved on this site, so I just thought I throw this out! Thanks for the help!

 

[David R]

Alright, as long as:
a) Your visits never overlap
b) You never have someone leave and arrive on the same day (depending on the type of hospital, it might, in which case I have no idea what you can do about it),
c) You only require data for one month (in my example, the month previous to running the query).

This should work:
queryPatientDates: based on tablePatients
Drag down field PatientID (FK from main table)
Calculated fields:
AdmitThreshold: DateSerial(Year(Date()),Month(Date())-1,1)
DischargeThreshold: DateAdd("d",-1,DateSerial(Year(Date()),Month(Date()),1))
NewAdmitDate: IIf([AdmitDate]<[AdmitThreshold],IIf([DischargeDate]>=[AdmitThreshold],[AdmitThreshold],[AdmitDate]),[AdmitDate])
NewDischargeDate: IIf([DischargeDate]>[DischargeThreshold],IIf([AdmitDate]<=[DischargeThreshold],[DischargeThreshold],[DischargeDate]),[DischargeDate])

queryPatientStay: based on queryPatientDates, turn on Totals (Sigma button)
Drag down field PatientID, Group By
Calculated fields:
Stay: Sum([NewDischargeDate]-[NewAdmitDate]+1), Expression
WhatMonth: Month([NewAdmitDate]), Where, Criteria: Month(Date())-1. Do not show this field. If you need to see what month it is, add this field:
MonthQueried: Format([NewAdmitDate],"mmmm"), Group By (or First, or Last)

Run this query and you should get summations, grouped by PatientID, for the previous month (April, in this case).

HTH,
David R


[This message has been edited by David R (edited 05-03-2002).]

 

[raskew]

Assuming that tblAdmissions was set up like this:

AdmissionID PatientID InDate OutDate
1 3 3/15/02 5/10/02
2 1 3/28/02 4/6/02
3 2 4/1/02
4 1 4/8/02 4/13/02
5 1 4/12/02 4/15/02


These two queries would allow you to specify the [mm/yyyy] e.g. 04/2002, to be displayed:

Query92:

SELECT PatientID, InDate, OutDate, [enter mm/yyyy] AS strMoYr, IIf([InDate]<DateValue([strMoYr]),DateValue([strMoYr]),[inDate]) AS myStartDate, Switch(IsNull([OutDate]) Or [outdate]>DateSerial(Year([mystartDate]),Month([myStartDate])+1,0),DateSerial(Year([mystartDate]),Month([myStartDate])+1,0),True,[outdate]) AS myEndDate, DateDiff("d",[myStartDate],[myEndDate])+1 AS NumDays
FROM tblAdmissions
WHERE (((tblAdmissions.InDate)<=DateSerial(Year(DateValue([enter mm/yyyy])),Month(DateValue([enter mm/yyyy]))+1,0)) AND ((tblAdmissions.OutDate) Is Null Or (tblAdmissions.OutDate)>DateValue([enter mm/yyyy])));

Query93:

SELECT PatientID, strMoYr, Sum(NumDays) AS SumOfNumDays
FROM Query92
GROUP BY PatientID, strMoYr;

 

[Liz H]

Can't wait to plug this in! Thanks for the help!