Malformed IF statement using formula tool

[smknowles]

 Attempting to code an operating statement into groups by account number. I believe it has something to do with the data type. Any help is appreciated. 

 

IF [Account #] = 5120
THEN "Rent"
elseif [Account #] =5920
OR [Account #] =5191
OR [Account #]=5993
OR [Account #]=5994
THEN "Other Rev"
elseif [Account #] = 6396.04 OR [Account #]= 6993
then "Activities and Social"
elseif [Account #] = 6210
OR [Account #]= 6311
OR [Account #]=6311.01
OR [Account #]=6311.02
OR [Account #]=6311.03
OR [Account #]= 6311.04
OR [Account #]= 6321
OR [Account #]= 6390
OR [Account #]= 6391
OR [Account #] = 6399
OR [Account #] = 6399.01
OR [Account #] = 6399.03
OR [Account #] = 6395
OR [Account #] = 6396
OR [Account #] = 6396.01
OR [Account #] = 6396.1
OR [Account #] = 6396.13
OR [Account #] = 6398
OR [Account #] = 6395
OR [Account #] = 6800
OR [Account #] = 6890
OR [Account #]=6891
OR [Account #] = 6892
then "Admin & General"
elseif [Account #]=6320
then "Actual Management Fees"
elseif [Account #]=6396.16
OR [Account #] =6396.18
then "Advestising and Marketing"
elseif [Account #]=6370
then "Bad Debt"
elseif [Account #]= 6396.03
OR [Account #]= 6933
OR [Account #]= 6932
then "Dietary"
elseif [Account #]= 6396.07
OR [Account #]= 6951
OR [Account #]=6952
then "Laundry and Housekeeping"
elseif [Account #]= 6538.01
then "Lease Expense"
elseif [Account #]= 6396.02
OR [Account #] = 6519
OR [Account #] = 6525
OR [Account #] = 6537
OR [Account #] = 6538.02
OR [Account #] = 6541
OR [Account #]= 6542
OR [Account #]= 6542.01
OR [Account #]= 6542
OR [Account #]= 6591
then "Maintenance"
elseif [Account #]=6397.01
OR [Account #]= 6712
OR [Account #]= 6719
then "Payroll Taxes & Benefits"
elseif [Account #]= 6397.05
OR [Account #]= 6720
OR [Account #]= 6723
then "Property and Liability Insurance"
elseif [Account #]= 6396.05
OR [Account #]= 6396.06
OR [Account #]= 6396.09
OR [Account #]= 6396.19
OR [Account #]= 6962
OR [Account #]= 6963
OR [Account #]= 6980
OR [Account #]= 6990
then "Resident Care"
elseif [Account #]= 6450
OR [Account #]= 6451
OR [Account #]= 6452
OR [Account #]= 6453
OR [Account #]= 6991
OR [Account #]= 6992
then "Utilities"
elseIF [Account #]= 6397.04
THEN "N/A"
ELSE "0"

 

 

[clmc9601 · Accepted solution]

Hi @smknowles, looks like you might have resolved your own issue (you probably just needed an "ENDIF" at the end), but I wanted to mention that there is a more efficient way to do this if you're interested! A batch macro could eliminate the need for this lengthy expression, but within the expression itself, you can replace these long OR statements with an IN statement. See below.

elseif [Account #] IN (6396.02, 6519, 6525, 6537, 6538.02, 6541, 6542, 6542.01, 6542, 6591)

the above expression is equivalent to the below expression (your original)

elseif [Account #]= 6396.02
OR [Account #] = 6519
OR [Account #] = 6525
OR [Account #] = 6537
OR [Account #] = 6538.02
OR [Account #] = 6541
OR [Account #]= 6542
OR [Account #]= 6542.01
OR [Account #]= 6542
OR [Account #]= 6591

[apathetichell]

This would also be more efficient as a second Input (either it's own Excel file or a text input) and then using a JOIN tool. That would allow you to change the values much more easily and it would look a lot cleaner..

[smknowles]

Beautiful, thanks for the assist! Does the IN function work across multiple data types? The issue I was running into was my [Account #] was formatted as a 'double' and my 'then' value output in the new column were 'v-string' data type. Once I changed both to 'v-string' data types and included "" around the [account #]'s the statement worked the way I intended. I'm wondering if the IN can match a double and output a v-string or vice versa. 

 

[clmc9601 · Accepted solution]

@smknowles, I believe if you test the condition against a string, it will work! You wouldn't have to change the data type of the actual field but only in the expression. For example:

if ToString([Account #],2) IN ("6309", "6609", "3345", etc)

This temporarily converts it to a string to test against string values, but it does not actually change the entire column to a string.