Iif query between 2 numbers

[gmazza76]

Good afternoon,

I am trying to do an Iif in an Access table to determine how long a person has worked with the team, but I am getting an error "Expression not Supported for conversion" and I have no idea what this means.
Am I missing something basic?

IIf([Experienced] Between 0 And 90,"0 - 3 Months",IIf([Experienced] Between 90 And 180,"3 - 6 Months", "Experienced"))

thanks in advance

 

[Joe4]

I know you can use "BETWEEN" in criteria, but I am not sure that you can use it in calculated columns that you want to return.
Try replacing this check:

[Experienced] Between 0 And 90

with something like this instead:

(([Experienced] >= 0) And ([Experienced] < 90))

and repeat that same logic for the other IIF part too.

 

[Micron]

The expressions you can put in table field validation are quite limited. I am quite sure that IIF is not one of them. That means while the suggestion seems to be a valid validation expression, it will force number entries to be from 0 to 89 and won't show strings like "0 - 3 Months". To get that, you'll have to validate with a form.