Formula using IIF, AND, MID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a calculated field (STARS MATCH)in a query using the IIF
statment. Examples of the fields and the data in those fields is shown below:

Field name=ORG, data=PD5
Field name=JON, data =PD5AB or PD59Q
Field name=TEC, data=AMAF or SAAA

The criteria is:
IF the 4th position of the JON is "0" or greater, AND it is "9" or less, the
calculated field would string together fields ORG,ORG,TEC (PD5PD5AMAF)
IF the 4th position does not meet the criteria THEN the string would be
ORG,"P9F",TEC (PD5P9FAMAF)

Here's the syntax I'm using - it doesn't work:
STARS
MATCH:IIF(AND(MID([JON],4,1)>="0",MID([JON],4,1)<="9"),[ORG]+[ORG]+[TEC],[ORG]+"P9F"+[TEC])

What am I doing wrong? I've tested each function and operator alone and they
work,but when I string it all together, it doesn't. I can't see where I've
omitted a comma or parentheses, but that must be it. Please help. Thanks.
 
Excel syntax doesn't work in Access VBA. Try the following

IIF(Mid(Jon,4) >="0" and Mid(Jon,4,1)
<="9,[ORG]+[ORG]+[TEC],[ORG]+"P9F"+[TEC])
 
Back
Top