greater than query

[bernie]

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

 

[MarkK]

I think you mean IIF(), and if so, search VBA help for "IIF Function" and you'll get detailed info.

 

[whitty15]

Wrong format

First, like 'lagbolt' said it is not IF that is used, like on Excel, it is IIF that is used.

In addition, i believe that the format of the if statement that you are using is incorrect. it should follow such rules...
IIF([Calculation/Expression], Result if True, Result if False)

I have made a quick alteration, see if this works..

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