IIF Statement - Further Issue

[Guest]

Hi All,

This post is further to a post earlier answered by Allan Murphy.

The statement below works perfectly.

=IIf([tblStaff.type]="Commission",[total]*[commissions],IIf([tblStaff.Type]="Wages",IIf([tblDaybook.Type]="Ax",[total]*[ComAx],IIf([tblDaybook.Type]="TDE",[Total]*[ComTrade],0))))

SOMEHOW i'd like add a further restriction to the above statement.

the field containing the string is formated to Currency and I'd like the
answer to reflect $0.00 if the field [TArrived] is during business hours.
Business hours are defined by fields [StartHours] and [EndHours]. These are
formated in medium time.

I think this statement should go after IIf([tblStaff.Type]="Wages but not
sure exactly where and how to type the string.

Any assist is appreciated.

Greg.

 

[Douglas J Steele]

You're not explaining it quite well enough, at least not for me.

Right now, you've got something like:

If [tblStaff.type]="Commission" Then
Value = [total]*[commissions]
Else
If [tblStaff.Type]="Wages" Then
If [tblDaybook.Type]="Ax" Then
Value = [total]*[ComAx]
Else
If([tblDaybook.Type]="TDE" Then
Value = [Total]*[ComTrade]
Else
Value = 0
End If
End If
End If
End If

Are you saying that if TArrived is during business hours, you want $0.00,
regardless of any other calculations, which would be the equivalent to:

If [TArrived] >= [StartHours] And [TArrived] <= [EndHours] Then
Value = 0
Else
If [tblStaff.type]="Commission" Then
Value = [total]*[commissions]
Else
If [tblStaff.Type]="Wages" Then
If [tblDaybook.Type]="Ax" Then
Value = [total]*[ComAx]
Else
If([tblDaybook.Type]="TDE" Then
Value = [Total]*[ComTrade]
Else
Value = 0
End If
End If
End If
End If
End If

or do you want something else?

 

[Guest]

Hi Doug,

Yes you are 95% correct when you said:

"Are you saying that if TArrived is during business hours, you want $0.00,
regardless of any other calculations, which would be the equivalent to:"

Except, I only want the above statement to apply if tblStaff.Type = "Wages".

Thanks

Greg

 

[Douglas J Steele]

So it sounds as though you want

If [tblStaff.type]="Commission" Then
Value = [total]*[commissions]
Else
If [tblStaff.Type]="Wages" Then
If [TArrived] >= [StartHours] And [TArrived] <= [EndHours] Then
Value = 0
Else
If [tblDaybook.Type]="Ax" Then
Value = [total]*[ComAx]
Else
If([tblDaybook.Type]="TDE" Then
Value = [Total]*[ComTrade]
Else
Value = 0
End If
End If
End If
End If
End If

Then

=IIf([tblStaff.type]="Commission",[total]*[commissions],IIf([tblStaff.Type]=
"Wages",IIf([TArrived >= [StartHours] And [TArrived] <=
[EndHours],0,IIf([tblDaybook.Type]="Ax",[total]*[ComAx],IIf([tblDaybook.Type
]="TDE",[Total]*[ComTrade],0)))))

Hopefully the way I've laid out the If statements make it easier for you to
understand how the IIf statements are nested.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Yes you are 95% correct when you said:

"Are you saying that if TArrived is during business hours, you want $0.00,
regardless of any other calculations, which would be the equivalent to:"

Except, I only want the above statement to apply if tblStaff.Type = "Wages".

Thanks

Greg

Hi All,

This post is further to a post earlier answered by Allan Murphy.

The statement below works perfectly.

=IIf([tblStaff.type]="Commission",[total]*[commissions],IIf([tblStaff.Type]=
"Wages",IIf([tblDaybook.Type]="Ax",[total]*[ComAx],IIf([tblDaybook.Type]="TD
E",[Total]*[ComTrade],0))))

SOMEHOW i'd like add a further restriction to the above statement.

the field containing the string is formated to Currency and I'd like the
answer to reflect $0.00 if the field [TArrived] is during business hours.
Business hours are defined by fields [StartHours] and [EndHours]. These are
formated in medium time.

I think this statement should go after IIf([tblStaff.Type]="Wages but not
sure exactly where and how to type the string.

Any assist is appreciated.

Greg.