quick question - strWhere with WHERE (1 Viewer)

[Misiek]

Hello all,

I'm struggling to find answer online, perhaps not asking right question.
In one of my forms I use Alan Browne's filtering code

http://allenbrowne.com/ser-62code.html

This shows all records with specific user:

    If Not IsNull(Me.cboUser) Then
        strWhere = strWhere & "([tUserFK] = """ & Me.cboUser & """) AND "
    End If

I want to show all records for a group of users.
In table T_user i have UserID, UserName, UserResource, where rescue is a yes/no field.

in standard SQL query this would work:

...
WHERE (UserResource=True)
...

I can't work out how to translate it into vba.
Can anyone advice please.
Thank you

 

[pbaldy]

Is this what you're after?

strWhere = strWhere & "([tUserFK] = """ & Me.cboUser & """) AND UserResource=True AND "

 

[Misiek]

Thank you.

I think so, will try it tomorrow at work.
can i skip this part?

""" & Me.cboUser & """

As I don't want to select any user, just click on button and the code will run in background giving me wanter result.

 

[pbaldy]

Oh sure, try

strWhere = strWhere & "UserResource=True AND "

 

[Misiek]

I tried this yesterday but when I run it it shows prompt to enter userResource instead of filtering. Like it doesn't know this filled name. The spelling is 100% correct.
I also tried TableName.fieldname = true.
But this gives the same prompt :/

 

[pbaldy]

Can attach the db here?

 

[Misiek]

please see stripped version.

 

[pbaldy]

The field name is correct, but the field is not selected in the query underlying the form.

 

[Misiek]

So I just need to simply add it to q_actionplanner?

 

[pbaldy]

Yes; did you try it?

 

[pbaldy]

Oh, I changed this back too:

strWhere = strWhere & "(uCommonResurce=true) AND "

 

[Misiek]

Yes works superb!
Would never think this was the problem.
Many thanks

 

[pbaldy]

Happy to help!

 

[Misiek]

Hello again,
Unfortunately I found another problem,

When I open the form and click Filter button, it gives 12 records, then click REPORT button and I still have 12 records. All of it is ok.

Please memorize 1st and 2nd record

issue 1.
When I click Common Resource button I only get 1 record, but should see 2. (the first 2 from previous filtering.

issue2.
the filter Common resource doesnt "save" and then pass strWhere to report.

Any advice on that, please?

Thank you


________________________________
update:

issue 1 resolved, added extra brackets

had:
(...) OR (...)

changed to:
((...) OR (...))


issue 2
Fixed. Command the opens report was applying original filter before running report. My error when coding.