11 Replies Latest reply on Aug 8, 2013 2:19 AM by ohannis

    If Statement quation


      Good Day guys,

      what's the best way or format to construct a function that will result the following.


      If ( DATE_FIELD ≥ DATE_FIELD_1 and DATE_FIELD ≤ DATE_FIELD_2); "Report"; "Don'r Report";


      In human language I am trying to check if the selected date (DATE_FIELD) is within the range of DATE_FIELD_1 and DATE_FIELD_2. If it is then show result 1 or result 2.


      It seems I cannot user and in the if statement.


      Please help.

        • 1. Re: If Statement quation

          Your statement's syntax is slightly off.


          If statement Syntax: If ( test ; resultOne ; resultTwo )


          If ( date_fielddate_field_1 and date_field date_field_2; "report"; "Don't Report")



          • 2. Re: If Statement quation

            Try this calc:


            Let ( [


            thisDate = DATE_FIELD ;

            firstDate = DATE_FIELD_1 ;

            lastDate = DATE_FIELD_2  ;

            inRange = If ( thisDate ≥ firstDate and thisDate ≤ lastDate ; 1 ) ] ;


            If ( inRange = 1 ; "Report" ; "Don't Report" ) )

            • 3. Re: If Statement quation


              I have tried the statent you mentioned but I still have a problem with the statment. check results below.



              8/6/2013          8/5/2013               8/82013                Report

              8/5/2013          8/1/2013               8/4/2013               Report << I guess the DATE_FIELD_2 is not being checked.

              • 4. Re: If Statement quation

                Keywords, Thanks alot this worked perfect.

                • 5. Re: If Statement quation

                  One thing to look at careful in such situations is to consider what will happen if any of these dates are blank (no value). 

                  • 6. Re: If Statement quation

                    Good point. For what it's worth, the effect in my calc is as follows:


                    thisDate blank—not in range

                    firstDate blank—in range

                    lastDate blank—not in range


                    thisDate AND firstDate blank—in range

                    thisDate AND lastDate blank—not in range

                    firstDate AND lastDate blank—not in range


                    I think it would be correct to summarise the logic here as: null can be LESS THAN, but cannot be GREATER THAN,  any value.


                    Another case could be that firstDate is later than lastDate; if so the result is not in range.

                    • 7. Re: If Statement quation

                      Well in my case this will do the trick. for the following reason.

                      Date_Field is the invoice date.

                      Date_Field_1 and Date_Filed_2 is user controlled. In this case the user selects the calander date from a drop down box.


                      On a differant note. Is there a way that I can force and field to be populated with an identical date. eg. I have a report layout with the following info


                      Part number     Description     Customer info      Amount     Invoice date     Week Start Date     Week End Date


                      In this situation user changes the Week Start Date and Week End Date. Is there a way i can make all the cells/Fields under the Weeks Start and Week End change value when i select a date ? This is handy if my report is long.



                      • 8. Re: If Statement quation

                        If the dates are selected for the purpose of a one off report could you make the fields global? Better still why not just enter Week Start Date and have Week End Date auto complete by calculation—Week Start Date + 7?

                        • 9. Re: If Statement quation

                          Good Idea,

                          I never used gloal fields. How can i get that done ?

                          • 10. Re: If Statement quation

                            Go to the field definitions (File –> Manage –> database), select the field name, click options button at bottom right, click on the storage tab, check "choose global storage (one value for all records)".

                            • 11. Re: If Statement quation

                              Works like a charm.