iif statements in a calculated field

[scbret]

I'm trying to make a calculated field give me a "Year-Quarter" (ex 2007-4) string with iif statements using a ship_date field.

Str(Year(Ship_date)) + "-" + iif(Str(Month(Ship_date)) <= "3","1", iif(Str(Month(Ship_date)) >= "4" AND Str(Month(Ship_date)) <= "6", "2", iif(Str(Month(Ship_date)) >= "7" AND Str(Month(Ship_date)) <= "9", "3", "4")))

All work except for Quarter 4 months, they come back like 2007-1 instead of 2007-4.

Could somebody show me what I'm doing wrong?

Thanks

[Aaron]

Ok, the problem here is that you're converting the month to a list of characters, also called a string, before performing the comparison. Let's work through the logic for a particular Ship_date in the fourth quarter, December 1, 2007.

For this particular date, Month(Ship_date) returns 12, where 12 is a number, and Str(Month(Ship_date)) returns "12", where "12" is a string. Now, when the formula evaluates, "12" as a string is compared with "3" as a string; and as a string, "12" is less than "3", since for strings, the individual characters of the string are compared from left to right when evaluating equality and "1" is less than "3". To see another example of this, create a calculated field which numbers the rows with RECNO(), convert the row numbers to strings with STR(RECNO()), then sort this calculated field. You'll see that the row numbers as strings follow a similar order: 1, 10, 11, ..., 2, ...

To fix your formula, compare the month of the ship_date to a number within each of the IIF() statements, then take the final resulting number and convert it to a string:

Code: Select all

Str(Year(Ship_date)) + "-" + Str(iif(Month(Ship_date) <= 3, 1, iif(Month(Ship_date) >= 4 AND Month(Ship_date) <= 6, 2, iif(Month(Ship_date) >= 7 AND Month(Ship_date) <= 9, 3, 4))))

[scbret]

Thanks Aaron...."logic" is usually my problem (lack of it)