Using the format function in a query

[gkl]

Dear All,

I have a database in Access 2013 which, amongst others, has a table for holding employee pay rates. I want to do a query that calculates a new field that will be saying how much their new pay rate would be should they be given a 10% pay rise.

It's all good and simple, I have a query that has a new field as:
New Pay Rate:Format(1.1 * [Pay Rate],"£#00.##")

My problem is that if an employee has a pay rate of 10, the display will show £11. as the . is taken by the Format function to be a literal character. Would it be possible to use Format in such a way that if a whole number is encountered the dot is not used and if one is encountered then the two first digits after the decimal point are used (if I use ; to specify different formats according to documentation, this would be for positive vs negative numbers).

Regards,
George

 

[CJ_London]

try New Pay Rate:Format(1.1 * [Pay Rate],"£#00.00")

also be aware that using format in a query like this returns a string, not a numerical value - so you won't be able to multiply it by a quantity for example. You should format it at the point of delivery (i.e. in a form or report control) and not before

 

[arnelgp]

New Pay Rate:Format(1.1*[Pay Rate], IIF((1.1 * [Pay Rate])-INT(1.1*[Pay Rate]) <> 0,"£#00.##", "£0"))