Can't filter my cross query with a form variable or temp variable. (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
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?
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
It works when I just put the number to filter, but I need to pull it from somewhere else.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
Hard to say without seeing what you're doing. What's the SQL of the query?
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
Code:
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;
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
What is glngUserID()? If it's a public function that returns an appropriate value, that should work. If it's a variable, it won't. You can't refer to variables in a query, they are only accessible within VBA.
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
I just tried to make a global variable and use that and it worked. I did it earlier and it didn't work. Strange.
Thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
You referred to a variable directly from the query? I'd love to see the db if you can attach it. To my knowledge you can't refer to variables from a query. You'd have a function that returned the value of the variable and call that in the query. Alternatively you can use a TempVar or refer to a form control.
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
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?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
I don't think it is difficult, but without seeing the function I can't say why it doesn't work. Can you attach the db here?
 

bastanu

AWF VIP
Local time
Today, 14:28
Joined
Apr 13, 2010
Messages
862
Can you at least show us the loop function that doesn't work?
 

Isaac

Lifelong Learner
Local time
Today, 14:28
Joined
Mar 14, 2017
Messages
5,556
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?
So you're not referring to a variable in a query; you're referring to a function in a query.

Right?
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
So I'll try to explain this better. I have some reports that I need by the loop (12-week cycle). I have a form where you can select which loop you want. I have a function tied to the combo box where the loop number is which is being pulled from a table. Here is the function I used.

Code:
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

For some reason, if I use the function in the query it works for loop 1 but when I choose loop 2, the subreports are empty.
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
So you're not referring to a variable in a query; you're referring to a function in a query.

Right?
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.
 

Isaac

Lifelong Learner
Local time
Today, 14:28
Joined
Mar 14, 2017
Messages
5,556
I think the next step would be to place a breakpoint on the first line of your function and debug it. See what is being returned line by line until you identify why during your number two loop it's not working. See precisely what value is coming out of that function.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
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?
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
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?
Yes, the box turns red when the item is selected.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
Can you attach a sample db that exhibits the problem?
 

Lkwdmntr

Registered User.
Local time
Today, 14:28
Joined
Jul 10, 2019
Messages
261
Ok, so after struggling with this for way too long, I will attach the database to see if this problem can get solved. There is really a lot to this program. To get to the report I'm talking about open the Main menu form and choose John Jones. He is the only one that has data for both loops. From there go to Reports and then select Reversal Diet By Plan Week and then the loop and then Output. It is not filtering either loop, but it is grouping the loops. Nice, but not what I need. When loop one is selected I need only the data from that loop to be displayed and so on for any further loops.
 

Attachments

  • FettlerHealthDBReImagined.zip
    3.5 MB · Views: 131

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:28
Joined
Aug 30, 2003
Messages
35,342
Your combo is a listbox. When you use the arrow to scroll down to 2, you have to actually click on the 2 to select it. Then the report will filter as desired. I'd just use a combo box.
 

Users who are viewing this thread

Top Bottom