Using code in a query

[Nelmo]

I am not sure if this is a query or VB problem.

I would like a field in a query to display the results of the calculations on the following fields in a [Percentage] field.

[REG YEAR] 1996, 1997, 1998 etc.
[MILES] a value list "<2500", "2500-18000", "18000+"

There are six possible outcomes based on the following criteria:
If the car is less than 4 years old and has done less than 2500 miles the value required is 35%. If the car is less than 4 years old and has done "2500-18000" then the value is 25% and so on. The possible values are, dependent on the age of the car and mileage, (35%, 25%, 15%, 26.25%, 18.75%, 15%).

I can write the If...Then...Else statements for the above.
e.g.

dim Percent as Long

If Year(Now())-[REG YEAR]<=4 And [MILES]="<2500" Then Percent=35%
and so on for each ElseIf.

I suspect a Select Case Statement would be more efficient but my knowledge of these is limited.
Either way how do I use these calculations in my [Percentage] field (using Build and IIF only allows two possible outcomes)

Thanks in advance NELMO

 

[Abby N]

Hello Nelmo. If you don't want to code a new function, you can solve you problem with what I call a cascading IIF statement. (I don't know if that's the real name for this. That's just what I call it.) It works by using an IIF statement as the result of another IIF statement. Below is an example. It looks at a field called 'number' which I have defined in my source table as an integer. I'm sure you'll have no problem understanding how it works once you've looked it over.

IIF([number] < 1,"Invalid",IIF([number] between 1 and 5,"Small",IIF([Number] between 6 and 10,"Medium","High")))

Hope this helps.

~Abby

 

[Pat Hartman]

Please don't post the same question under multiple topics. It just causes confusion and wastes people's time.