Your statement's syntax is slightly off.
If statement Syntax: If ( test ; resultOne ; resultTwo )
If ( date_field≥date_field_1 and date_field≤ date_field_2; "report"; "Don't Report")
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" ) )
I have tried the statent you mentioned but I still have a problem with the statment. check results below.
DATE_FIELD DATE_FIELD_1 DATE_FIELD_2 RESULT
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.
Keywords, Thanks alot this worked perfect.
One thing to look at careful in such situations is to consider what will happen if any of these dates are blank (no value).
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.
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.
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?
I never used gloal fields. How can i get that done ?
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)".
Works like a charm.