Multiple conditions in IF statement
I'm a newbie and cannot figure out how to set up an IF statement with 2 conditions. I'm working with a customer database that has both domestic and foreign addresses. The original data file loses the leading zero of US zips. (Changing the data in the original file is not an option as I don't have control over the file.) So, I was trying to write an IF statement that would check two conditions 1) that the Zip field is less than 5 digits AND 2) That the Country field = "United States." The formula I used is below:
If (Zip<5 and Country="United States" ; Zip ; 0&Zip )
I have this formula defined for another field FullZip which is a calculation field where the calculation result is text. (Text is needed so that the - is maintained in Zip+4 addresses.) This works fine for the US addresses and appropriately adds the missing leading zero. But it does not respect the AND so that records where the country field does not equal "United States" have a leading zero added. So, what have I missed in the formula?
Any insights would be appreciated.
FMP 9.0 v3
2.5 GHZ Intel Core 2 Duo MBP 4 GB Ram
Mac OS X 10.5.8