Help with an expression

[harryfraser]

Hi,

I'm relatively new to access but can just about make my way around. I just wanted help with building an expression to use in a query.

Basically i want to put in a calculated date field called AwardExpiry, based on the AwardStart date in a table entitled "Institutions".

For backgrounds sake there are 4 types of awards, bronze to platinum. I also want to incorporate a parameter query into the design, so here's what I want to happen:

When the query is run its asks the user for the award type (user enters gold, silver, bronze or platinum). If it is any of the first 3 it will return a date in the award expiry field two years from the award start. However if it is platinum it will return a date one year from the award start date.

I've guessed that you use an If statement for this, something along the lines of:

=If([Award]="Gold" or "Silver" or "bronze",DateAdd("Y","2",[AwardStart])) OR =If[Award]="Platinum",DateAdd("Y","1",[AwardStart]))

Can someone help me please, i'm also a little unsure as to where I would insert this in the query design view. I have no idea how to use VBA so explain in WYSIWYG format would be appreciated.

Thanks

Harry

 

[Galaxiom]

In an expression you need the Immediate If function, IIF.

The syntax requires each test of the condition to be fully explicit.

IIF(Something=Another OR Somethingelse=YetAnother, ValueIfTrue, ValueIfFalse)

They can be nested. The outcomes can be another IIF function.

BTW You should also represent the metallic strings as integers with a lookup table to display the strings as required.

 

[vbaInet]

I will add to Galaxiom's post by showing you how to write multiple IIF()s that relates to your situation:

IIF(Something=Another OR Somethingelse=YetAnother OR OtherThing = OneMore, ValueIfTrue, IIF(TheOther = "Platinum", ValueIfTrue, Null))