Autofill Field Based On Another Field

[tcrwt]

I have a problem which I'm sure is easy to solve. I've never used access before and I'm working my way through. I have searched around but I can't find the answer... so, here's my problem.

I have two fields that are relevant, currently within the same table.

The first field is called RAC, and it is a combobox with two options (well, three?): 12 Months, 24 Months, and blank (this is the length, if any, of the policy the customer takes out). The next field is called RAC Price, and it simply needs to return 80 if RAC is 12 Months, 150 if RAC is 24 Months, and 0 if RAC is blank. Preferably these numbers will be preceded by £, but that's not vital.

I, being very new, tried to set the RAC Price field to a calculate field with the formula:



IIf( [RAC]="12 Months","80","") AND IIf( [RAC]="24 Months","150","") AND IIf( [RAC]="","0","")

But unsurprisingly this didn't work. It returns 0 for an empty RAC field, and -1 for any value in the field, no matter what it is. I guess it's to do with the fact that two of the conditions will return false no matter what's in the RAC field, so they're giving conflicting info.

Anyway, could I please have some help? All I need is for RAC Price to return something based on RAC. It doesn't matter if this happens in the field input in the form or in the column in the table.

Thanks in advance!

[June7]

Don't use AND operator. RAC can be only one value.

You need a nested IIf:

IIf([RAC]="12 Months",80,IIf([RAC]="24 Months",150,0))

[tcrwt]

It worked like a charm. Thank you so much! I can't believe it was so close to what I put!