how to use IIf Function

[malikge]

Hi,
I have a situation, in reports I want a summary to count the total working days of each vehicle type.

Here is what I want.

[U]VehicleType 	VehicleNumber	 Days[/U]
H. Truck	   GA  3434       2
F.B. TRL	   AS 3222	  7
F.B. TRL	   HY 34324	  1
L.B. TRL	   DD 3434	  2

	Summary:
		H.Truck = 2 
		F.B. TRL = 8	
		L.B. TRL = 2

By using following expression:

=Sum(IIf(VehicleType= " H.Truck ", 1, 0))
=Sum(IIf(VehicleType= " F.B. TRL ", 1, 0))
=Sum(IIf(VehicleType= " L.B. TRL ", 1, 0))

I get :

Summary:
	H.Truck = 1
	F.B. TRL = 2	
	L.B. TRL = 1

This returns the vehicle types, that are presented in the report.

Is there a way by which I can get the total "working days" of each vehicle type.

Thanks...

 

[RainLover]

You need to use DSum.

Look at access Help under Domain Functions.

 

[plog]

You need to use the value in the table for Days, not just 1. You are essentially using the SUM function to COUNT. This is what it should be:

=Sum(IIf(VehicleType= " H.Truck ", Days, 0))
=Sum(IIf(VehicleType= " F.B. TRL ", Days, 0))
=Sum(IIf(VehicleType= " L.B. TRL ", Days, 0))

 

[malikge]

RainLover:
I tried Dsum, but it didn't work.

=DSum("[Days]","[Vehicle Type]='H. Truck'")

Plog:

=Sum(IIf(VehicleType= " H.Truck ", Days, 0))

When I use this expression and run the report, it shows the dialog box asking for Vehicle Type and Days and did not show the total days.

 

[RainLover]

The first part of the statement should be the Table Name, The Second should be the Field.

EDIT

The first part of the statement should be the Field, The Second should be the Table Name.

So "Days" is the Table and "Vehicle Type" is the Field.

Is this correct.

Perhaps you need to reread up on DSum again.

But get this working first.

DSum("Table","Field")

Work out the condition later.

 

[RainLover]

=Sum(IIf(VehicleType= " H.Truck ", Days, 0))

Does Vehicle Type have a Space or Not.?

Suggest you read up on Naming Conventions and get rid of Spaces in Names.

Also where and how are you using Plogg's Code?

You may also need to introduce an NZ Function as well.

 

[Simon_MT]

If you use Groups and sort on Truck Type , with a a header and/or footer you should be able just to Sum([Days]) to each Truck Type.

Simon

 

[malikge]

The first part of the statement should be the Table Name, The Second should be the Field.

So "Days" is the Table and "Vehicle Type" is the Field.

Is this correct.

Perhaps you need to reread up on DSum again.

But get this working first.

DSum("Table","Field")

Work out the condition later.

Sorry..., After rereading DSum I knew about that.
Problem is this I have a query which takes data from 3 tables, and that query also takes the start and end date as parameters. So I think DSum cannot be used now.

=Sum(IIf(VehicleType= " H.Truck ", Days, 0))

Does Vehicle Type have a Space or Not.?

Suggest you read up on Naming Conventions and get rid of Spaces in Names.

Also where and how are you using Plogg's Code?

You may also need to introduce an NZ Function as well.

Yes vehicle type have space, but after removing the space it still shows the dialog box.
I am using Plogg's code inside the report, by opening the report in Design View and placing a Text Box and entering the code in it.

If you use Groups and sort on Truck Type , with a a header and/or footer you should be able just to Sum([Days]) to each Truck Type.

Simon

If I used that, then how can I get working days of a vehicle's type separately?

 

[RainLover]

Plogg's Solution would be the best.

=Sum(IIf([Vehicle Type]="H.Truck",[Days],0))

Tested

Place this in Report Footer

 

[malikge]

Sorry for being a dumb... I am just started learning Access.

In the table I have a "Deployment From" and "Deployment To" columns and in query I calculate the Days by
Days: ([Deployment To]-[Deployment From])

In report footer I place a "text box" and in the unbound area of the text box I place the code:
=Sum(IIf([Vehicle Type]="H.Truck",[Days],0))
but still when I run the report it shows the dialog box asking for the Days.

 

[RainLover]

Try

=Sum(IIf([Vehicle Type]="H.Truck",[Deployment To]-[Deployment From]),0))

UNTESTED

Have I suggested getting rid of those spaces. They will cause grief.

EDIT

This may not work.

Give me some time and I will test.

A little busy just now.

 

[malikge]

Tried that, did not work.

And yes, I have removed those spaces.

Thanks.. will be waiting for your reply

 

[RainLover]

=Sum(IIf([VehicleType]="H.Truck",[DeploymentTo]-[DeploymentFrom],0))

This works only for the one VehicleType. We have to write more for the others.

But try this one first and see if that works.

Tried and Tested.

Edit

I assume DeploymentTo and DeploymentFrom are Numbers not Dates.

 

[malikge]

DeploymentTo and DeploymentFrom are dates.

 

[RainLover]

Is there anything else you need to tell me.

I am running out of time. Have to go Bush. Plane leaves at 10:00AM tomorrow. My Time. So have a good think about what else you need.

 

[RainLover]

Create a Query with all the fields that you need.

Add this as a new Field in the query.

Days: DateDiff('d',[DeploymentFrom],[DeploymentTo])

Then in your Report use

=Sum(IIf([VehicleType]="H.Truck",[Days],0))

PS

Please include your Windows Version and Access Version as part of your Signature.

Different Version have different methods, especially A 2007 and A 2010.

 

[RainLover]

Create a Query and include all the relevant fields.

Add an extra Field and put this in.

Days: DateDiff('d',[DeploymentFrom],[DeploymentTo])

Then use this in the Report with the new query as the Record Source.

=Sum(IIf([VehicleType]="H.Truck",[Days],0))

I tested this and it works.

PS

Could you place your Windows and Access versions in your Signature as there are some differences that we need to know about from time to time.

 

[malikge]

Thanks for bearing with me...

and sorry for not clarifying everything.

I have created a new query.
Added an extra Field and put:
Days: DateDiff('d',[DeploymentFrom],[DeploymentTo])
I gives the days difference

In report I added this:
=Sum(IIf([VehicleType]="H.Truck",[Days],0))

When I run the report in the text box it shows:
#Error

And I am using Windows7 and Access 2007

 

[RainLover]

Did you change the Record Source to that Query.

What happens if you change Days to a number like 999

=Sum(IIf([VehicleType]="H.Truck",999,0))

If the others don't jump back in send them a PM.

Gone for a few days. Bye.

 

[RainLover]

Back from my trip.

Did you slove the problem. Please let me know.