Convert Excel Formula to access

[Caben]

Hi All,

I never use access but have to create a field in a table with a formula in it. The formula works fine in excel. Does anybody know how to change it to access?

The Excel formula is:

=IF(A3>=400,"Honour",IF((A3>=300)*AND(A3<400),"Merit",IF((A3>=250)*AND(A3<300),"Pass",IF(A3<250,"Fail"))))

I know it should be something like this:

IIf([Score Total]>=400,"Honour",
iIF(([Score Total]>=300*AND<400,"Merit",
iIF(([Score Total]>=250*AND<300,"Pass",
iIF([Score Total]<250,"Fail"
))))

but it is not excepting it. Can anyone find the errors of my ways?

Thanks in advance Caben

 

[alansidman]

In Access, you cannot put an expression (formula) in a table. This happens in Queries and Forms. The syntax for IF-THEN statements in Access differs slightly from Excel.

Here is a link to show you the syntax. Look at the last example to see the syntax for AND issues.

MS Access: iif Function

 

[Caben]

I sorted it. Thanks to anyone who was considering help me with it.

This is the expression should it be useful to anybody else:

IIf([Score Total]>=400,"Honour",IIf(([Score Total]>=300) And ([Score Total]<400),"Merit",IIf(([Score Total]>=250) And ([Score Total]<300),"Pass","Fail")))

Hope this helps somebody.

Caben

 

[Caben]

Thanks for the info Alan. Not sure when expressions were included in Access, I'm using 2010. They can be added to a table in that version anyway and I got it sorted. Thanks for your reply.

 

[SydneyGeek]

2010 is the first version to support calculated fields in tables. For compatibility it's best to stick with the solution Alan provided, which wroks with all versions of Access.

Denis