Multi IF expression not displaying all the data

[Jerry Tack]

I've entered an expression in a table field that will display the right mileage rate dependent on the post code entered and the type of vehicle used. The idea is that if a car is going from the BH post zone then I should automatically see the £1.15 rate and a MPV leaving from the GL zone should display the £1.40 rate.

The problem I have is that when I show the table none of the MPV rates are displayed other than the "SP" and "SN" which both happen to be the last post zones in the individual IIF statement and happen to precede the AND statement.

The actual expression is below:

IIf([Post Zone]="BH" Or [Post Zone]="SO" Or [Post Zone]="PO" Or [Post Zone]="SP" And [Veh Type]="Car","£1.15",IIf([Post Zone]="BH" Or [Post Zone]="SO" Or [Post Zone]="PO" Or [Post Zone]="SP" And [Veh Type]="MPV","£1.20",IIf([Post Zone]="BA" Or [Post Zone]="BS" Or [Post Zone]="GL" Or [Post Zone]="SN" And [Veh Type]="Car","£1.35",IIf([Post Zone]="BA" Or [Post Zone]="BS" Or [Post Zone]="GL" Or [Post Zone]="SN" And [Veh Type]="MPV","£1.40",""))))

Where have I gone wrong? I have got more post codes to enter but I won't bother yet until I can get this fixed. I have contemplated creating an Update query to automatically load these in but I can't figure out how to create on that will do multiple updates of a single field and I would prefer to just enter the data into the table

 

[Stoneboysteve]

Jerry Tack,

What program are you using?

Stoneboysteve

 

[Jerry Tack]

Hi Stoneboysteve

It's MS Access 2007

 

[Jerry Tack]

I've now re-entered all the info in again just to make sure and when I enter the vehicle type of car and the relevant post zone I get the correct information but if I enter the vehicle type of MPV and the post zone I still get the car rate. IE Enter Post Zone BH and Veh Type Car I get £1.15 which is correct. I enter Post Zone BH and Veh Type MPV I get £1.15 when it should be £1.20. The code seems to be ignoring the AND filter.

 

[RobXMiller]

You are working way too hard. try this. In Access Create go to Module and create a new module.
Past this code:
Function EightPlus(MyValue As Long) As Long
' takes a number in the query window and adds 8
EightPlus = MyValue + 8
' eightplus returns value
End Function

Save the module
back in your qbe grid - use the new function - pass in a number, mine uses a counter ID
substitute your own table and field, so long as the field is numeric
> put this in field of QBE grid: Answer: eightplus([tblOne].[ID])

You can do the same with your long winded IIF code.
Code allows for comments, error trapping, step-by-step debugging, to name a few advantages.

Stop working so hard and take the plunge to learn code. It is reuseable too.