I really need some help on this one

[rhay]

I asked this before but wasn't able to get it working, here is what I need to do:

I have a field called "passengers" based upon the number in this field, I need it to calculate the number of trucks I need.

For every 100 passengers and any portion of 100 I need 2 trucks. For example if I have 120 passengers, I need it to auto calculate and fill in the field "trucks" with 2.

I'm using an IIF statement now, but if the number is below 50 it doesn't take it into account.

Your help is appreciated, thanks!

Robert

 

[Alexandre]

I think you could use a formula instead of your iif statement: someting like

NumberOfTrucks = (NumberOfPassengers \ 100)+1

If I am not mistaken the \ gives you the integer part of the result of the division ex: 1 for 1,1 or 1,9
0 for 0,5, etc.
If not, look for a similar function in the help, there is certainly one doing that I just don t remember its name at the moment.

 

[rhay]

I started out with exactly that, however as I said in my initial post, the formula does not take into account the portions of 100.

Using that formula, if I have 130 passengers it only calculates for 1 truck when I actually need 2.

Robert

 

[Rich]

I tried Alexandre's formula it returned the results you wanted. Is numofpass a number/long integer?

 

[rhay]

Yes, it does work to some extent, however if the number of passengers is 151 or higher with that formula it will give you 3 trucks..when only 2 are needed.

Robert

 

[zunan]

rhay
I'm sorry I don't know more details right now but I do know that there is a way of preceeding the calculation with an indicator that determines wether to round the answer up or down. I will do some searching and get back to you.

 

[Rich]

Not sure if this will come out correctly but it's taken from a query with the formula as posted.
fldPassengers NumberOfTrucks
150 2
59 1
134 2
216 3
83 1
183 2
416 5
40 1
65 1
99 1
100 2
151 2

 

[Rich@ITTC]

Hi Robert

Try this one - for me it works.

Truck: (IIf(([Passenger]=0),0,(IIf([Passenger] Mod 100=0 Or [Passenger] Mod 100>=50,([Passenger]/100),([Passenger]/100)+1))))

This gives:

Passenger   Truck
   0             0
   1             1
  50            1
  99            1
 100            1
 101            2
 149            2
 150            2
 151            2
 200            2
 201            3

I can e-mail you a very small database to show that it does the trick. Sorry that I didn't give you the right expression/calculation a couple of days ago but I was doing it from memory ... and with nested IIfs that isn't such a good idea!

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 04-07-2001).]