If(date<"01/01/2013";amount;0) would be your calculation.
Make sure the date field is date type.
Yes the condition should be like this
If(date<"01/01/2013";amount;0) and the field must be Dtae type field.
you may also use the GateAsDate() fucntion to converrt both to date if they are not.
date < Date ( 1 ; 1 ; 2013 ) ; amount
Date> Date ( 12 ; 31 ; 2011) and Date < Date ( 1 ; 1 ; 2013 ) and Type="Pledge" ; Amount
In the expression: if(Date<1/1/2013;Amount;0)
FileMaker interprets 1/1/2013 as 1 divided by 1 divided by 2013. That evaluates as a decimal less than 1 so it will always be an amount less than any date in your date field short of leaving the field empty.
That's why you have to use either the date function as raybaudi posted or GetAsDate ( "1/1/2013" ) to get FileMaker to interpret that data as a date.
To PhilModJunk..appreciated the explanation of why it didn't work. Just not sure if you make a field type Date why it doesn't treat it like one?
The recommended If(date<"01/01/2013";amount;0) did not work (see screenshot)and you can see in the left box that the Type is Date. Is there another setting in Type Date that is causing the fail?
I did try the Case forwarded by raybaudi and the 2nd calc worked. Is the 1st calc necessary and if so, how do you nest them to be accepted because it didn't like it when I put both expressions in?
I know this is elementary to you guys but usually if I can get the hang of how the software is thinking it makes it easier for me to learn.
The first cal was for your first example.
You'll need only the second one.
There is nothing wrong with the field of type date. Dates are stored as a number counting the number of days elapsed between 12/31/0000 and the date entered into that field. "01/01/2013" is a text string so you are comparing an integer number stored in that date field to quoted text.
That's why I used GetAsDate ( "01/01/2013" ) in my example. That function takes the quoted text and produces the needed intenger value so that it can be treated as a date. Date ( 1 ; 1 ; 2013 ) does the same.
Ok that makes sense. Now to actually GetAsDate....it is not an option in the Specify Calculation View menu of functions. I guess this must be learned elsewhere. This syntax didn't work.
GetAsDate ( "01/01/2013" )
It is indeed available in the list of functions in the Specify Calculation Dialog. You should look again.
Here's the syntax that you should be using:
If ( date < GetAsDate ( "01/01/2013" ) ; amount ; 0 )