Using OR in an IIF statement

[Bluezman]

I'm wondering how I can us an OR as criteria in an IIF statement.

For example, here is a textbox that is currently working:

=IIf(IsDate([txtPrev]),DateDiff("d",[txtPrev],[ActivityDate]),"0")

I would like to go one step further and have it look at another field, and depending on the value in that field, i.e. 1 or 6 or 8, return the number of days, but if the value is 2 or 3 or 4 or 5 or 7, return a 0 for that record.

Can multiple OR's exist in this IIf statement?

Thanks!!

Bluez

 

[itsmarkdavies]

You can embed Iif`s within Iif`s. Try something like the following :-

txtResult.Text = IIf(IsDate([txtPrev]), (IIf(txtDays.Text = 1 Or 6 Or 8, DateDiff("d", [txtPrev], [ActivityDate]), "0")), "0")

 

[Bluezman]

Thanks Mark, will try that out later this morning. I think I remember trying that at one time, but I must have written some part of it incorrectly. I'll let you know how it worked.

Bluez

 

[Bluezman]

Following Pat's suggestion, the calculations come out as I wanted them. Thank you Pat

If I might, one additional thing (and this might be better posted to the FORMS group, but since this situation is already posted here it might be easier to figure out), now that I have the calculated text boxes working correctly, I'd like to do a SUM for the column. Is this possible using another hidden text box?

Bluez

 

[Bluezman]

Pat, I've created a control [DaysSum] in the subform footer and made the control source:

=Sum(IIf(IsDate([txtPrev]),(IIf([ActivityTypeID]=9 Or [ActivityTypeID]=10 Or [ActivityTypeID]=11 Or [ActivityTypeID]=14 Or [ActivityTypeID]=17 Or [ActivityTypeID]=18,DateDiff("d",[txtPrev],[ActivityDate]),0)),0))

Then made a textbox on the main form and set the control source to:

=[tblActivity subform].[Form]![DaysSum]

and now get #Error on the main form control.

What am I doing wrong?

Bluez

 

[Bluezman]

anyone?

 

[Bluezman]

oh well

 

[Bluezman]

Hi Pat, Not sure I completely understand but let me explain a little more.

Using your suggestion, I made a textbox on subform footer called DaysSum and put the code that you suggested into the control source for that textbox.

Now, on the Mainform I put another textbox, called it txtTotalDays, and set it's control source to =[tblActivity subform].[Form]![DaysSum].

If this isn't right, a pointer on the correct way would be greatly appreciated.

Bluez

 

[Bluezman]

When looking at the subform in form view, [DaysSum] shows #Error.

When looking at my mainform, the subform footer is not showing.

 

[Bluezman]

Thinking that something must be getting lost in translation, here is a complete rundown on my subform:

The Subform [tblActivity Subform] has the following record source:

SELECT [tblActivity].[ActivityPK], [tblActivity].[ActivityID], [tblActivity].[ActivityDate], [tblActivity].[ActivityTypeID], [tblStatusTypes].[Type], [tblActivity].[ActivityChapter], [tblActivity].[ActivityState] FROM tblStatusTypes INNER JOIN tblActivity ON [tblStatusTypes].[MemberTypeID]=[tblActivity].[ActivityTypeID] ORDER BY [tblActivity].[ActivityID], [tblActivity].[ActivityDate], [tblActivity].[ActivityTypeID];

In the Details section, this gives me the fields of, [ActivityID] [ActivityDate] [ActivityTypeID] [Activity Chapter] and [Activity State]. I have chosen not to show [ActivityPK] on the subform.

I then have a hidden textbox [txtPrev] which uses Rich's "PrevRecVal" module. It has the following control source:

=PrevRecVal([Form],"ActivityPK",[ActivityPK],"ActivityDate")

I then have a calculated textbox [DaysOut] that has the following as it's control source:

=IIf(IsDate([txtPrev]),(IIf([ActivityTypeID]=9 Or [ActivityTypeID]=10 Or [ActivityTypeID]=11 Or [ActivityTypeID]=14 Or [ActivityTypeID]=17 Or [ActivityTypeID]=18,DateDiff("d",[txtPrev],[ActivityDate]),0)),0)

This works perfectly to bring me the days difference between records, if the ActivityTypeID's are in the previous record.

This is the point where Pat recommended that I put a textbox [DaysSum] in the subform footer and use SUM function. Pat stated that I could not reference the control [DaysOut] directly (since it's calculated) and that I would have to use SUM and the calculation that [DaysOut] used. I therefore used the following as the control source for [DaysSum]:

=Sum(IIf(IsDate([txtPrev]),(IIf([ActivityTypeID]=9 Or [ActivityTypeID]=10 Or [ActivityTypeID]=11 Or [ActivityTypeID]=14 Or [ActivityTypeID]=17 Or [ActivityTypeID]=18,DateDiff("d",[txtPrev],[ActivityDate]),0)),0))

This is were I get an #error, in the [DaysSum] field.

I hope this helps anyone trying to help figure this out.

Bluez

 

[Bluezman]

I believe (and here is where I will probably show my ignorance) the name of the control that holds the Sum() is DaysSum (located in the form footer) and it is a unique name.

If this is not what you're asking me for, please enlighten me on what I should do to get that information.

Now apparently, the =Sum(... itself isn't working correctly, as this is where the #error is first occuring. I'm wondering why it would work in the details section [DaysOut], but not when summed?

Bluez

Added:

Pat, after banging my head against a wall for some time, I think I finally pounded in what you need to know about the Control of the subform.

I used a wizard to create the subform "tblActivity Subform", and in reading I've found that the wizard, by default, names the control the same as the subform itself. (I thought this was a no no?) In any case, the control is also named "tblActivity Subform", which I will change if you think it needs to be.

Just to be certain I'm looking at the Control and not the subform, the Control properties is the area where I specify the Master/Child links right?

Bluez

 

[Bluezman]

Hi Pat,

Why would the exact syntax work when the =IIf(... is placed in the details section, but not when it's encapsulated by the Sum()?

As for the control having the same name as a field in the record source, I've triple checked and there is not a field with the same name.

Lastly, I've tried to put the code directly into a query, but with the reference to [txtPrev] and the underlying module PrevRecVal needing to reference a form, I haven't gotten this to work in a query.

I've started another thread on this in the Forms area and have attached a sample mdb file (datediff2.zip) for anyone that would like to try their hand at getting the sum() to work. This might give you a better example, than I have, about what I'm working with.

Bluez