Using OR in an IIF statement

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

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")

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

You should not surround numeric values with quotes:

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

To use multiple conditions:

=IIf(Me.SomeField = 1 OR Me.SomeField = 6 OR Me.SomeField = 8, Me.NumDays, 0)

itsmarkdavies's syntax will NOT work. You MUST repeat the field name. Also, you need to be very careful when using the .text property since it is ONLY available when the field in question has the focus. This mistake is commonly made by people who know VB but are new to Access. This property is not the same in Access controls as it is in VB controls. Take a look at this thread where I posted the help entry or look it up for yourself:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40219&highlight=text+property

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

Add a control to the form or subform footer. You'll need to repeat the calculation inside the Sum() function since you cannot reference a control name. So -
=Sum(SomeControlName) is WRONG

but

=Sum(some calculation) is OK

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

anyone?

oh well

Can you see the calculated control in the subform footer and is the value correct?

[tblActivity subform] needs to be the name of the subform CONTROL not the name of the actual subform.

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

But you didn't answer my questions. Can you see the sum on the subform? And are you sure that you are referring to the subform CONTROL rather than the subform itself?

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

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

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

OK we're getting there. You answered question number 1. How about question number 2. WHAT IS THE NAME OF THE CONTROL that contains the Sum()? Is it UNIQUE or does it duplicate some name in your recordsource?

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

Try moving the calculation of the days dif to a query to make sure the syntax is ok:

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)

You either have a syntax error In the Sum() statement or the control name (name on the Other tab) is the same as the name of some field in the recordsource.

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