Problems with IIF Statement

[Guest]

Below is my setup:
Column Expr1= # days of absence
Column Expr4=represents if that was a whole day or half day.
The values in column Expr4 are 1=whole day .5=half day
I need to get a count of the whole days in my report then multiple that
result to expr1. The current formula I have is this below:

=Sum(IIf([expr4]="1",1,0))*[expr1]

This does not give me the correct count. Please could someone advise.

Thanks

 

[jleckrone]

Try =DCount("Expr4","YourTableNameHere", "Expr4 = '1'") * [Expr1]

 

[Guest]

I'm getting the word Error on my report.There is one problem with this
expression where you say enter your table name. I'm not for sure on what to
put there since I am using multiple tables and expressions.
The expr4 and Expr1 come from multiple expressions for example:

Expr1: DateDiff("d",[JobStartDate],[JobEnddate])+1 --This gives me the
number of days absence

Expr2: DateDiff("h",[JobStarttime],[JobEndtime]) --This gives me the number
of hours worked, but just for that job, it doesn't know that there could be
mulitple days.
Expr4: IIf([Expr2]<=4,".5","1")--This is defining my Whole days and Half days

 

[Duane Hookom]

You really need to kick your development up a notch and not accept
expression names like expr1 or expr4. If expr4 is numeric then ditch the
quotes around "1".
This might also work if used in the proper section of the report.
=Sum( Abs([expr4]=1)*[expr1] )

If you provided a few sample records, displayed results, and expected
results, we could probably be of better help.

 

[Marshall Barton]

Below is my setup:
Column Expr1= # days of absence
Column Expr4=represents if that was a whole day or half day.
The values in column Expr4 are 1=whole day .5=half day
I need to get a count of the whole days in my report then multiple that
result to expr1. The current formula I have is this below:

=Sum(IIf([expr4]="1",1,0))*[expr1]

I would think that should be:

=Sum(IIf(expr4=1, expr1, 0))

 

[Guest]

Ok, I will try to do a little bit more detail. I tried the below expression
and got Type mismatch. Here is how my report should look:
Results
Should Be This
School Adams Middle
Employee Position Computer Education
How Many Half Days Absence 0
0
How Many Whole Days Absence 13
18
Total Absence 18
18

Alot of my calculations have been done in query, which is where my Expr1 and
Expr4 come from.
The total absence Count is correct. The formula used for that is:
=Sum([Expr1]*[expr4])

Half day count is correct, Formula is:
=Sum(IIf([expr4]=".5",1,0))

Whole Day is wrong and orginial formula that I was using was:

=Sum(IIf([expr4]="1",1,0))

I'm still very new to this, and I am catching on, but any more suggestions
would be greatly appreciated.




The total

You really need to kick your development up a notch and not accept
expression names like expr1 or expr4. If expr4 is numeric then ditch the
quotes around "1".
This might also work if used in the proper section of the report.
=Sum( Abs([expr4]=1)*[expr1] )

If you provided a few sample records, displayed results, and expected
results, we could probably be of better help.
--
Duane Hookom
MS Access MVP

Below is my setup:
Column Expr1= # days of absence
Column Expr4=represents if that was a whole day or half day.
The values in column Expr4 are 1=whole day .5=half day
I need to get a count of the whole days in my report then multiple that
result to expr1. The current formula I have is this below:

=Sum(IIf([expr4]="1",1,0))*[expr1]

This does not give me the correct count. Please could someone advise.

Thanks

 

[Duane Hookom]

Isn't Expr4 numeric? If so, kill the quotes...
=Sum(IIf([expr4]=.5,1,0))

You can create descriptive column names in your query.
--
Duane Hookom
MS Access MVP

Ok, I will try to do a little bit more detail. I tried the below
expression
and got Type mismatch. Here is how my report should look:
Results
Should Be This
School Adams Middle
Employee Position Computer Education
How Many Half Days Absence 0
0
How Many Whole Days Absence 13
18
Total Absence 18
18

Alot of my calculations have been done in query, which is where my Expr1
and
Expr4 come from.
The total absence Count is correct. The formula used for that is:
=Sum([Expr1]*[expr4])

Half day count is correct, Formula is:
=Sum(IIf([expr4]=".5",1,0))

Whole Day is wrong and orginial formula that I was using was:

=Sum(IIf([expr4]="1",1,0))

I'm still very new to this, and I am catching on, but any more suggestions
would be greatly appreciated.




The total

You really need to kick your development up a notch and not accept
expression names like expr1 or expr4. If expr4 is numeric then ditch the
quotes around "1".
This might also work if used in the proper section of the report.
=Sum( Abs([expr4]=1)*[expr1] )

If you provided a few sample records, displayed results, and expected
results, we could probably be of better help.
--
Duane Hookom
MS Access MVP

Below is my setup:
Column Expr1= # days of absence
Column Expr4=represents if that was a whole day or half day.
The values in column Expr4 are 1=whole day .5=half day
I need to get a count of the whole days in my report then multiple that
result to expr1. The current formula I have is this below:

=Sum(IIf([expr4]="1",1,0))*[expr1]

This does not give me the correct count. Please could someone advise.

Thanks