A formula a query run off your days table will work. Use the Size function. Can you post you last attempt using a formula Query and we can help get it working.
I had to go back a few iterations to reconstruct that attempt, but this is what I have:
Size(
GetRecords("{99.OAF.'"&[Date]&"'}AND{77.OBF.'"&[Date]&"'}", "[_DBID_TASKS]")
)
I confirmed that all three fields (Date in this table, and fields 77 and 99 in the table being queried) are the same type. 77 is the assignment start date and 99 is the end date (which remains blank until the assignment is completed).
The formula is accepted, but returns zero records.
I see two issues.
The first is that the table name should not be in quotes [_DBID_TASKS].
Size(
GetRecords("{99.OAF.'"&[Date]&"'}AND{77.OBF.'"&[Date]&"'}", [_DBID_TASKS])
)
The second is that the end date fid 99 might be blank. I suggest that you make a new field in the Assignments table called [End Date or Today] with a formula of
IF(IsNull([End Date]), Today(), [End Date]) so that it will always have a date. The use that field's Field ID instead of 99 in your Formula Query.
Oh my goodness, the quotes were the problem! I actually started with a formula copied and pasted from the Formula queries Help page specifically so I didn't have to worry about getting all the punctuation right. It may need to be corrected:
Your other solution for the blank end date is excellent, that will be helpful for many other situations as well. Thank you much!
