If Greater than Criteria 1

[berniecliffdon]

Hello All

I'd appreciate some help with a (for me) pretty complicated query Im attempting. Im trying to achieve 1 answer if the source is less then 7, and another answer if it is greater.

I've been searching through archived posts and picking up tips to the extent that I think im in the right ball park, but Im making a mistake with invalid syntex.

in plain english Im trying to calculate

If:
date range from bookings1 is 7 or less
I want to multiply the result of the date range
By the daily hire rate (from a different table)
If:
date range from booking! is over 7
I want to multiply the result of the date range
by the daily hire rate
then multiply that answer by 0.8
(to achieve a 20% discount on the total)

Cost:IF([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]<7)*[Car]![Daily Hire Rate] IF ([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]>7)*[Car]![Daily Hire Rate]*0.8)

I've tried quite a few variations on that and it reads right to me, but im obviously missing some vital formatting.

I know that criteria I had for calculating the date range, and multiplying that by the daily hire rate works because I've tested that and saved that version

Any help on this would be greatly appreciated
Thanks
George

 

[PHV]

What about this ?
Cost: ([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date])*[Car]![Daily Hire Rate]*IIf([Forms]![Bookings1]![End Date]-[Forms]![Bookings1]![Start Date]>7,0.8,1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[berniecliffdon]

Thanks PH, that looks good. I won't be able to try it until later today, but i'll lt u know how it goes.

 

[berniecliffdon]

Magic, worked 1st time.
Thanks ph. That's this database essentially finished now.
I'll try and repay this good forum karma In a couple of years when Im qualified and I actualy now what Im doing : )