You could do a ExecuteSQL like this:
ExecuteSQL ( "
WHERE MONTH(NextContact)=? AND YEAR(NextContact)=?
" ; "" ; "" ; Month ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ))
Thank you that worked perfectly.
I thought that had worked however it is giving me a result for each record regardless of wether there is date in the main field "NextContact" or wether it is empty.
Can you offer any help on your original idea.
Where do you put the calculation?
As this calculation is context independent, you would probably fill a global or $$variable with it (best through a script) and place it on a overview layout, or in the header-/footer-/summery-part of your report. It looks like you want to put it as calculated-field in your table holding the records - that would give you the same result on every record.
Ideally I was trying to show in the Header,
I am trying to create 3 total fields that work of 3 calculation fields that show
1. Number Of Calls Due Today
2. Number Of Calls overdue all together
3. Number of Calls that are in this current month
I have done the first 2 using Calculation field and Case option.
The 3rd one is causing me the issue, Ideally I was trying to set that where a record has current months date in it was set to 1, then another Summary Field counting the total.
I am looking to use the below I think?!?
MonthName(Next Contact Date) & " " & Year(Next Contact Date);1
And then a summary field to calculate total number of records.
>3. Number of Calls that are in this current month
An alternate method would be to create a related table.
- From Date
- To Date
- Calculate Related Values
Pre-populate the new table with date ranges (i.e. From 5/1/2016; To 5/31/2016). Calculation looks back at your original table and counts the related records that fall between the 'from' and 'to' date fields.
Can you post a sample file? Would be easier to explain...
Quick db created.
- Main Table
- z_Next Contact
Field Def (Main Table)
- Next Contact (as Date)
Field Def (z_Next Contact)
- dteFrom (as Date)
- dteTo (as Date)
- c_CountMonth (as Calculation)
= Count ( Main Table::NextContact )
- NextContact >= dteFrom
- NextContact <= dteTo
Calc Number of Records.zip 67.1 K