Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Reports / Printing / May 2006

Tip: Looking for answers? Try searching our database.

Problems with IIF Statement

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
KRB - 19 May 2006 20:48 GMT
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@hotmail.com - 19 May 2006 21:21 GMT
Try =DCount("Expr4","YourTableNameHere", "Expr4 = '1'") * [Expr1]
KRB - 19 May 2006 21:50 GMT
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

> Try =DCount("Expr4","YourTableNameHere", "Expr4 = '1'") * [Expr1]
Duane Hookom - 19 May 2006 22:25 GMT
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.
Signature

Duane Hookom
MS Access MVP

> Below is my setup:
> Column Expr1= # days of absence
[quoted text clipped - 8 lines]
>
> Thanks
KRB - 22 May 2006 16:09 GMT
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
[quoted text clipped - 16 lines]
> >
> > Thanks
Duane Hookom - 22 May 2006 16:24 GMT
Isn't Expr4 numeric? If so, kill the quotes...
=Sum(IIf([expr4]=.5,1,0))

You can create descriptive column names in your query.
Signature

Duane Hookom
MS Access MVP

> Ok, I will try to do a little bit more detail. I tried the below
> expression
[quoted text clipped - 48 lines]
>> >
>> > Thanks
Marshall Barton - 20 May 2006 02:15 GMT
>Below is my setup:
>Column Expr1= # days of absence
[quoted text clipped - 4 lines]
>
>=Sum(IIf([expr4]="1",1,0))*[expr1]

I would think that should be:

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

Signature

Marsh
MVP [MS Access]

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.