A problem with an IIf statement in Access query

[luciegiles]

Hi,

In an Access query I am using an IIf statement below to calculate the time between two dates; this is defined as [Period1]:

Period1: IIf([UnavailabilityEndDate1]<Date(),DateDiff('d',[UnavailabilityStartDate1],[UnavailabilityEndDate1],DateDiff('d',[UnavailabilityStartDate1],Date())))

In the same query I am using the following IIf statement to return [Period1] or a 0:

ExcludePeriod1: IIf([UnavailabilityStartDate1] Between [EffectiveStartDate] And [DateTreatmentStarted1] Or [DateTreatmentStarted1] Is Null,[Period1],0)

The problem I have is with the 0; this works fine if the above statement is simply:

ExcludePeriod1: IIf([UnavailabilityStartDate1] Between [EffectiveStartDate] And [DateTreatmentStarted1],[Period1],0)

but as soon as I add the additional criteria 'or [DateTreatmentSt arted1] is null' to the statement it stops returning a 0. It does return [Period1] though.

I can't fathom why the 0 is not returned as a result of the change. I've tried a nested IIf also, but the same result.

[UnavailabilityS tartDate1], [UnavailabilityE ndDate1], [EffectiveStartD ate] and [DateTreatmentSt arted1] are all date fields.

Appreciate any help
LG

[NeoPa]

Try using parentheses to make your intention clear :

ExcludePeriod1: IIf(([UnavailabilityStartDate1] Between [EffectiveStartDate] And [DateTreatmentStarted1]) Or ([DateTreatmentStarted1] Is Null),[Period1],0)

Otherwise it will try to work out what you mean with too little information and may get it wrong (The value of [DateTreatmentSt arted1] Or [DateTreatmentSt arted1] isn't a result you want it to deal with).

[luciegiles]

I'm afraid that didn't work. I had tried using parentheses to break up the statement before I posted (although not exactly as you suggest) but that didn't work either.

[luciegiles]

If it helps a 0 is returned for the records where [DateTreatmentSt arted1] is not null but not for those where it is null.

[NeoPa]

Bearing in mind that whenever you add or remove something from the criteria part then the criteria is no longer the same, can you provide us with some information about the circumstances where these formulas are not working. If it isn't a basic syntax problem as you've now indicated, then we need a full questions with the details in from which we can work. Obviously an IIF() should return parameter 2 in some circumstances and parameter 3 in others. Saying it only returns parameter 2 doesn't even indicate a problem unless you give information that makes it clear that the criteria are not being processed as expected. That would require the results returned; the criteria used; the data in all of the referenced fields included in the formula. This data for each example is what is required to enable anyone to determine what is going wrong.

[luciegiles]

Ok, here goes.

The attached file shows the data that is used in this query, what has been returned and what I expected to be returned.

[UnavailabilityS tartDate1], [UnavailabilityE ndDate1], [EffectiveStartD ate] and [DateTreatmentSt arted1] are all date fields. [Period1] and [ExcludePeriod1] are calculated fields giving time in days.

Ultimately what I want is the time between [EffectiveStartD ate] and [DateTreatmentSt arted1], minus any Periods of unavailability ([Period1]), in days. What I am trying to do here is calculate any Periods of unavailability i.e. [Period1] and determine whether it should be subtracted or not in [ExcludePeriod1].

[Period 1] should calculate the time between [UnavailabilityS tartDate1] and [UnavailabilityE ndDate1] or the time between [UnavailabilityS tartDate1] and today, when [UnavailabilityE ndDate1] is in the future or null.

Period1: IIf([UnavailabilityEndDate1]<=Date(),DateDiff('d',[UnavailabilityStartDate1],[UnavailabilityEndDate1],DateDiff('d',[UnavailabilityStartDate1],Date())))

Period1 should only be subtracted if it falls between [EffectiveStartD ate] and [DateTreatmentSt arted1] or if it falls after [EffectiveStartD ate] and [DateTreatmentSt arted1] is null. This is determined in [ExcludePeriod1] as follows; if the time period does not meet the criteria for exclusion then I want the calculated field to display a 0.

ExcludePeriod1: IIf(([UnavailabilityStartDate1] Between [EffectiveStartDate] And [DateTreatmentStarted1]) Or ([DateTreatmentStarted1] Is Null),[Period1],0)

It is perfectly acceptable for a record not to have any Periods of unavailability, as shown in Records 3 and 4, who have null [UnavailabilityS tartDate1] and [UnavailabilityE ndDate1]. In [ExcludePeriod1] these should show 0 also.

I hope this is clearer, please ask if not.
LG

[Jerry Maiapu]

I think you should revisit the last criteria Iff condition. You do not get what you expect simply because your main value in which the Iff condition is based on is null so it nullifies the rest of the condition ie it returns null or blank and that why I believe you have a couple of blanks.
To solve the problem make sure to take care of any null dates values. For example ifUnavailabilityS tartDate1 is blank
the rest of the evaluation will be null. So do something like IIF(isnull([UnavailabilityS tartDate1],0 else something else) etc..

Handle null values for the 3 Bolded fields and should be fine.

ExcludePeriod1: IIf([[B]UnavailabilityStartDate1[/B]] Between [[B]EffectiveStartDate[/B]] And [B][DateTreatmentStarted1][/B] Or [DateTreatmentStarted1] Is Null,[Period1],0)

Hope this could help..

Passing by...

[luciegiles]

Hi Jerry,

thanks ever so much for the advice; I played around with the order of things and came up with the following:

Period1: IIf([UnavailabilityEndDate1] Is Null,DateDiff('d',[UnavailabilityStartDate1],Date()),IIf([UnavailabilityEndDate1]>=Date(),DateDiff('d',[UnavailabilityStartDate1],Date()),DateDiff('d',[UnavailabilityStartDate1],[UnavailabilityEndDate1])))

ExcludePeriod1: IIf([UnavailabilityStartDate1] Is Null,0,IIf([UnavailabilityStartDate1] Between [EffectiveStartDate] And [DateTreatmentStarted1] Or [DateTreatmentStarted1] Is Null,[Period1],0))

the expression for Period1 is kind of huge but on testing it seems to do what I want.

Thanks again, much appreciated
L

[NeoPa]

Lucie,

As that appears to be the issue (Well called Jerry), you may find using the Nz() function more appropriate :

Period1: DateDiff('d',[UnavailabilityStartDate1],IIf(Nz([UnavailabilityEndDate1],Date())<Date(),[UnavailabilityEndDate1],Date()))

You can use the same idea for the other one.

BTW Sorry I haven't been able to look into this in more detail yet. I've been quite busy and the example data wasn't visible in the thread. I expect you felt the data was too enormous, but where possible it's always better to include the question and all relevant data visibly in the thread. I would have got around to it eventually, but sorting out files takes much more time and effort than reading it all together on the same page, so I needed to wait for a longer window of opportunity.

[luciegiles]

NeoPa,

No worries and thanks for the info re Nz(), I wasn't aware of that one.

I tried to include my data in the thread as it wasn't particulaly big. I had tabulated it to make it easier to follow but couldn't format the table within the thread so decide to attach it instead.

Any advice on formatiing in the thread?

[NeoPa]

I'm glad that's answered your question Lucy :-)

I'll answer the other one via PM as I don't want to stray off-topic here. Look out for a new PM very shortly.

[luciegiles]

NeoPa,

one question in relation to the expression you suggest above which is much more succint than mine - what is <Date() referring to, is it [UnavalabilitySt artDate1] at the start of the DateDiff?

L

[luciegiles]

ok, think I figured it out - if the whole expression (Nz([UnavailabilityE ndDate1],Date()) is less than Date() then use [UnavailabilityE ndDate1]. Otherwise use Date(). ??

[NeoPa]

Spot on Lucie :-)