IIF Statement - Further Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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?
 
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
 
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!)


Greg said:
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







Greg said:
Hi All,

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

The statement below works perfectly.
Click to expand...
=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.
Click to expand...
Click to expand...
 
Back
Top