Hi, I have a report with 3 subforms all need to be filtered by the value on a field in the main report. I tried to filter the queries, but it keeps saying it doesn't recognize the forms value or temp var value. I'm lost, any suggestions?
TRANSFORM First(IIf([qrptReversalDietByWeek]![IncludedInDiet],"X","")) AS IncludedInDiet
SELECT qrptReversalDietByWeek.UserIDFK, qrptReversalDietByWeek.Loop, qrptReversalDietByWeek.FoodGroupID, qrptReversalDietByWeek.FoodGroup, qrptReversalDietByWeek.AlwaysInclude
FROM qrptReversalDietByWeek
WHERE (((qrptReversalDietByWeek.UserIDFK)=glngUserID()))
GROUP BY qrptReversalDietByWeek.UserIDFK, qrptReversalDietByWeek.Loop, qrptReversalDietByWeek.FoodGroupID, qrptReversalDietByWeek.FoodGroup, qrptReversalDietByWeek.AlwaysInclude
PIVOT qrptReversalDietByWeek.WeekNumber;
So you're not referring to a variable in a query; you're referring to a function in a query.So in my program, every twelve weeks is a loop. The function I made is to grab which loop is being accessed for the report. I have a function for the user id which works perfectly, but the one I created for the loop doesn't work when you switch from loop 1 to loop 2, only works for the first loop. Boggling! Why is it so difficult to filter data in the subforms?
Function gintLoop() As Integer
On Error GoTo Err_PROC
If gfIsLoaded("frmSelectLoopReport") Then
If IsNull(Forms!frmSelectLoopReport!cboLoop) = False Then
gintLoop = Forms!frmSelectLoopReport!cboLoop
Else
gintLoop = 0
End If
Else
gintLoop = 0
End If
Exit_PROC:
Exit Function
Err_PROC:
MsgBox Error$
Resume Exit_PROC
End Function
I used the function in the query and it worked for loop 1 but not for loop two. Funny thing is that if I just put the number 2 in the criteria for the query it works. Not sure why this is so difficult to obtain.So you're not referring to a variable in a query; you're referring to a function in a query.
Right?
Yes, the box turns red when the item is selected.Shot in the dark, but have you made sure to hit Enter or Tab after you enter a 2 in the combo before running the query?