D
Derek Wittman
Good morning
I have a subform (query) that doesn't inherently have a linked field to the main form. My main form has
List_Yea
Text_Perio
Text_PeriodStart =DateAdd("d",28*([Text_Period]-1),DLookUp("[Year_StartDate]","PeriodHistory","[Fiscal_Year]=List_Year")
Text_PeriodEnd =DateAdd("d",27,[PeriodStart]
The List_Year comes fro
Table [PeriodHistory
Fields: Fiscal_Year Year_StartDat
SQL for my Query is (watch the wrap!
SELECT [Standards_Update Log].[Control_Number], [Standards_Update_Log].Division, [Standards_Update_Log].[Date_Requested], [Standards_Update_Log].Department, [Standards_Update_Log].[Operation_ID], [Standards_Update_Log].AislesLevel, [Standards_Update_Log].[Old_tandard], [Standards_Update_Log].[New_Standard], [Standards_Update_Log].[Date_Completed
FROM [Standards_Update_Log], [PeriodHistory
GROUP BY [Standards_Update_Log].[Control_Number], [Standards_Update_Log].Division, [Standards_Update_Log].[Date_Requested], [Standards_Update_Log].Department, [Standards_Update_Log].[Operation_ID], [Standards_Update_Log].AislesLevel, [Standards_Update_Log].[Old_Standard], [Standards_Update_Log].[New_Standard], [Standards_Update_Log].[Date_Completed], Year([Date_Requested]), IIf(Year([Date_Requested])=2003,Int(([Date_Requested]-#2/2/2003#)/28)+1,Int(([Date_Requested]-#1/5/2004#)/28)+1), Year([Date_Completed]), IIf(Year([Date_Completed])=2003,Int(([Date_Completed]-#2/2/2003#)/28)+1,Int(([Date_Completed]-#1/5/2004#)/28)+1
HAVING ((([Standards_Update_Log].[Date_Requested]) Between [forms]![PeriodQuery].[periodstart] And [forms]![PeriodQuery].[periodend])) OR ((([Standards_Update_Log].[Date_Completed]) Between [forms]![PeriodQuery].[periodstart] And [forms]![PeriodQuery].[periodend]))
The PeriodStart and PeriodEnd are calculated values based on the other 2 fields and are requeried when List_Year and Text_Period are updated. I wanted this to be realtime. I have a button that initially performed a runquery with the criteria of having "Date_Requested between Text_PeriodStart and Text_PeriodEnd or Date_Completed between Text_PeriodStart and Text_PeriodEnd" only in the Access GUI.
Instead of opening the query in a separate object window, I'd like to have it be a subquery that is requeried with the new criteria whenever I click the button after changing the criteria.
The problem is that the form and subform don't have fields that would be linked since the only thing I really want to consider are the date ranges created in the main form
Can someone help me out
Thanks
Dere
I have a subform (query) that doesn't inherently have a linked field to the main form. My main form has
List_Yea
Text_Perio
Text_PeriodStart =DateAdd("d",28*([Text_Period]-1),DLookUp("[Year_StartDate]","PeriodHistory","[Fiscal_Year]=List_Year")
Text_PeriodEnd =DateAdd("d",27,[PeriodStart]
The List_Year comes fro
Table [PeriodHistory
Fields: Fiscal_Year Year_StartDat
SQL for my Query is (watch the wrap!
SELECT [Standards_Update Log].[Control_Number], [Standards_Update_Log].Division, [Standards_Update_Log].[Date_Requested], [Standards_Update_Log].Department, [Standards_Update_Log].[Operation_ID], [Standards_Update_Log].AislesLevel, [Standards_Update_Log].[Old_tandard], [Standards_Update_Log].[New_Standard], [Standards_Update_Log].[Date_Completed
FROM [Standards_Update_Log], [PeriodHistory
GROUP BY [Standards_Update_Log].[Control_Number], [Standards_Update_Log].Division, [Standards_Update_Log].[Date_Requested], [Standards_Update_Log].Department, [Standards_Update_Log].[Operation_ID], [Standards_Update_Log].AislesLevel, [Standards_Update_Log].[Old_Standard], [Standards_Update_Log].[New_Standard], [Standards_Update_Log].[Date_Completed], Year([Date_Requested]), IIf(Year([Date_Requested])=2003,Int(([Date_Requested]-#2/2/2003#)/28)+1,Int(([Date_Requested]-#1/5/2004#)/28)+1), Year([Date_Completed]), IIf(Year([Date_Completed])=2003,Int(([Date_Completed]-#2/2/2003#)/28)+1,Int(([Date_Completed]-#1/5/2004#)/28)+1
HAVING ((([Standards_Update_Log].[Date_Requested]) Between [forms]![PeriodQuery].[periodstart] And [forms]![PeriodQuery].[periodend])) OR ((([Standards_Update_Log].[Date_Completed]) Between [forms]![PeriodQuery].[periodstart] And [forms]![PeriodQuery].[periodend]))
The PeriodStart and PeriodEnd are calculated values based on the other 2 fields and are requeried when List_Year and Text_Period are updated. I wanted this to be realtime. I have a button that initially performed a runquery with the criteria of having "Date_Requested between Text_PeriodStart and Text_PeriodEnd or Date_Completed between Text_PeriodStart and Text_PeriodEnd" only in the Access GUI.
Instead of opening the query in a separate object window, I'd like to have it be a subquery that is requeried with the new criteria whenever I click the button after changing the criteria.
The problem is that the form and subform don't have fields that would be linked since the only thing I really want to consider are the date ranges created in the main form
Can someone help me out
Thanks
Dere