How is the date range specified? To dates in date fields? A calculated range of dates based on the value of Get ( CurrentDate) ? Or ???
Is there a field of type date defined in Stagehand_Shifts to use for limiting the records to a date range?
And example of your query showing one of your attempts at a WHERE clause might be helpful in helping us to help you.
For the date range I am planning on using a calculation field from the Interface table cDateoffirstportal, that calculates the date for the first day in the calendar, then I just need that same day + 7 to get my date range, I believe I need to say "BETWEEN cDateoffirstportal AND cDateoffirstportal +7"
cDateoffirstportal is as follows
Let ( [
FirstDayOfTheMonth = DayName ( Date ( gSelectedMonth ; 1 ; gSelectedYear ) ) ] ;
Date ( gSelectedMonth ; 2 ; gSelectedYear ) -
FirstDayOfTheMonth = "Monday" ; 3 ;
FirstDayOfTheMonth = "Tuesday" ; 4 ;
FirstDayOfTheMonth = "Wednesday" ; 5 ;
FirstDayOfTheMonth = "Thursday" ; 6 ;
FirstDayOfTheMonth = "Friday" ; 7 ;
FirstDayOfTheMonth = "Saturday" ; 1 ;
FirstDayOfTheMonth = "Sunday" ; 2 ) )
Yes each of the Stagehand_Shifts has a Shift_Day_Start and Shift_Day_End Field
Assuming that the shift_day fields defined in stagehand_shifts are defined as date fields and not text, try this query:
GROUP BY Employee_ID
WHERE ? >= Stagehand_Shifts.Shift_Day_Start and ? <= Stagehand_Shifts.Shift_Day_End"
; "," ; "" ; cDateoffirstportal ; cDateoffirstportal )