Using ROUND and CHOOSE in a query

[DC27]

Hi there,

I'm trying to copy an Excel formula into Access 2010

my formula (currently not working) looks like this:

PRICE*(Round(Choose(MATCH 0,2,2.5,3),2.5,2.2,2.1),0)-0.01)

The idea being that the PRICE will be multiplied by a matched number, if within a PRICE of 0-2, it will be multiplied by 2.5
THEN rounded up
Then 1p taken off to give you your lovely £3.99 price

I think some of the Excel forumlas dont work in Access possibly? So if anyone can help, this will save me exporting to excel and running a macro in there every time.

Thanks for any suggestions
DC

 

[Alansidman]

The round and Choose functions work the same in Access as Excel. There is no Match function in Access.

Here is a list of most of the Access functions with links to their syntax.

http://www.techonthenet.com/access/functions/index_alpha.php

If you are doing this calculation in a form, you may be better off using a Case Select in VBA.

Here is a link for that.

http://www.techonthenet.com/access/functions/advanced/case.php

Alan