Great than Less than Function

[brendafos]

I'm writing my first Function.

I want to create a field called Facility based on a range value from the Account number field.

The account numbers are in ranges by the Facility.

I'm getting an error.  I think it due to how I've syntaxed by logic.  I'd appreciate some help.

MALFORMED IF STATEMENT

PARSE ERROR AT CHAR(27):

Here's my function code:::

IF [Visit ID] >= 100000000 AND <= 199000000
THEN "GSM"
ELSEIF [Visit ID]>= 200000000 AND <= 299000000
THEN "LMC"
ELSEIF [Visit ID]>= 300000000 AND <= 399000000
THEN "SJD"
ELSEIF [Visit ID]>= 400000000 AND <= 499000000
THEN "SVB"
ELSEIF [Visit ID]>= 500000000 AND <= 549000000
THEN "HRH"
ELSEIF [Visit ID]>= 550000000 AND <= 649000000
THEN "SJB"
ELSEIF [Visit ID]>= 650000000 AND <= 799000000
THEN "SFT"
ELSEIF [Visit ID]>= 800000000 AND <= 899000000
THEN "SMG"
ELSE "Error"
ENDIF

[MarqueeCrew · Accepted solution]

F [Visit ID] >= 100000000 AND [Visit ID]<= 199000000 THEN "GSM" ELSEIF [Visit ID]>= 200000000 AND [Visit ID]<= 299000000 THEN "LMC" ELSEIF [Visit ID]>= 300000000 AND [Visit ID]<= 399000000 THEN "SJD" ELSEIF [Visit ID]>= 400000000 AND [Visit ID]<= 499000000 THEN "SVB" ELSEIF [Visit ID]>= 500000000 AND [Visit ID]<= 549000000 THEN "HRH" ELSEIF [Visit ID]>= 550000000 AND [Visit ID]<= 649000000 THEN "SJB" ELSEIF [Visit ID]>= 650000000 AND [Visit ID]<= 799000000 THEN "SFT" ELSEIF [Visit ID]>= 800000000 AND [Visit ID]<= 899000000 THEN "SMG" ELSE "Error" ENDIF

happy new year,

mark

[jdunkerley79]

As you are learning the formula tool, might be worth looking at the switch function as an alternative.

Assuming all values from x00000000 to x99999999, you could do something like 

SWITCH(FLOOR([Field1]/100000000),"Error",
	1, "GSM", 
	2, "LMC",
...
)

If you need to reject x99000000 to x99999999 then you could do that as a conditional first, e.g.:

IF MOD([Field1],100000000) > 99000000 
THEN "Error"
ELSE
	SWITCH(FLOOR([Field1]/100000000),"Error",
		1, "GSM", 
		2, "LMC",
...)
ENDIF