calculating daily rates in certain circumstances

[LearnersPermit]

I have a vehicles database. I have a text box that calculates the kms travelled. =Sum([KMs_Travelled])and in a later box I calculate the cost using an ever changing mileage rate. This has been working correctly for some time.

Now I have some vehicles that will be charged by a daily rate and I'm not quite sure how to write that statement. I got this far. =DateDiff("d",[DateOut],[DateRtn]) but am not sure how to set it up to run only if VehicleType =3.

I'm continuing to look through the forum but have not yet found an answer.

Thank you

 

[SoCalAccessPro]

That's gonna be a complex syntax in your query, but not impossible.

=iif([tablename].[VehicleType]=3,DateDiff("d",[DateOut],[DateRtn]),Sum([KMs_Travelled)).

You'll have to play with the placement of the parantheses and bracketing to get it to work, but that's the basic idea.

Also, if Vehicle type is not a number (text) enclose it it commas. I.E. - '3'.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[MichaelRed]

Calculate both for all. Have a flag to denote which "Rate" applies. Makes the system more flexible.




MichaelRed