Filters in A report

[Neil_in_Japan]

On a report, I have a txtField that is receiving data from a lookup:

txtHRTech =[HR Tech].[Column](1)

Column 0 is the ID No.
Column 1 is the First Name Last Name
Column 2 is the Email Address

This field is part of a 'group' and all the HRTech data is group together. Works great!

I want to be able to run a filter, via a button, on that report to only display the HRTech's name.

Button will be, for me:

Private sub Neil_Click()

Me.Filter = "[txtHRTech]= Neil"
Me.FilterOn = True

End Sub

I have also tried:

Private sub Neil_Click()

dim tmpHRTech = variant

tmpHRTech = "Neil"

DoCmd.ApplyFilter , "[txtHRTech] = tmpHRTech"

End Sub


I have also tried a macro with the same criteria.

Each time I am prompted with a dialog box asking for the txtHRTech's entry. Even if I type the correct name, the filter is ignored.

Any ideas?

Thanks in advance!

 

[namliam]

Are we talking about a form or a report?
Me is only applicable to the current object, usually a form if you are talking about something you can put Niel into...

Even so... Working versions of what you currently have....

Me.Filter = "[txtHRTech]= ""Neil"""

Private sub Neil_Click()

    dim tmpHRTech [B][U]AS[/U][/B] variant

    tmpHRTech = "Neil"

    DoCmd.ApplyFilter , "[txtHRTech] = """ & tmpHRTech & """"

End Sub

Though if you truely need this in a report you probably need to send this there, for which I direct you to research the openReport method which allows you to send a "WHERE" clause to the report which works much the same way as above samples...

NOTE
"[txtHRTech] = """ & tmpHRTech & """"
Works if txtHRTech is a text field

For dates: "[txtHRTech] = "#" & format(tmpHRTech, "MM/DD/YYYY") & "#"
NOTE that dates must be in US Date formats (MM/DD/YYYY)

For numbers: "[txtHRTech] = " & tmpHRTech & ""

P.S. please use code tags when you post code, see the link in my signature on "how too"

 

[Neil_in_Japan]

Are we talking about a form or a report?
Me is only applicable to the current object, usually a form if you are talking about something you can put Niel into...

Even so... Working versions of what you currently have....

Me.Filter = "[txtHRTech]= ""Neil"""

Private sub Neil_Click()

    dim tmpHRTech [B][U]AS[/U][/B] variant

    tmpHRTech = "Neil"

    DoCmd.ApplyFilter , "[txtHRTech] = """ & tmpHRTech & """"

End Sub

Though if you truely need this in a report you probably need to send this there, for which I direct you to research the openReport method which allows you to send a "WHERE" clause to the report which works much the same way as above samples...

NOTE
"[txtHRTech] = """ & tmpHRTech & """"
Works if txtHRTech is a text field

For dates: "[txtHRTech] = "#" & format(tmpHRTech, "MM/DD/YYYY") & "#"
NOTE that dates must be in US Date formats (MM/DD/YYYY)

For numbers: "[txtHRTech] = " & tmpHRTech & ""

P.S. please use code tags when you post code, see the link in my signature on "how too"

Thank you for your help: However, the code still doesn't work. As I stated before, I am working within a report and only want to filter some of the information, from time to time, based on a field.

The field txtHRTech is a text field. I will hide the field from view when done, but I can see the content in the report.

When I click the button, I am prompted for the name of the person I want to filter on. The name is NOT passed to the filter from within the code.

txtHRTech field is in a group & sorted section.:banghead:

BUT, do I need to add a text field, txtHRTech, to the table upon which the report is based? That would seem terribly inefficient, but I need this to work.

Any ideas?

 

[namliam]

Well the best idea are best practice idea's

You dont want to use the prompt of the report, its ugly, restrictive and has no real possibilties of error trapping.

You want to have a (popup) form open before the report comes that will allow you to to ask for any input possible, than based on that input create a where statement that you send into the report using the Docmd.openreport method

 

[Neil_in_Japan]

Well the best idea are best practice idea's

You dont want to use the prompt of the report, its ugly, restrictive and has no real possibilties of error trapping.

You want to have a (popup) form open before the report comes that will allow you to to ask for any input possible, than based on that input create a where statement that you send into the report using the Docmd.openreport method

I like your idea a great deal. However, it isn't applicable in this case. My end users would balk if they had to answer a query every time they used the app to see data. My boss also likes the 30K view of the data a report yields.

A report allows me to show all the data then, via buttons, filter what they wish to see based on particular status/criteria. Ironically, I thought I had this licked as I have working filter buttons now - just not the name same field.

The report also allows for large memo fields to be displayed, where a table does not.

A report allows the data to be displayed in a sorted, group, nested context, a table does not.

Opening a form then closing or opening and choosing next, next is also inefficient.

Regrettably, I don't have any other tools to use. sharepoint et al. I will toss this 'feature' out.

But thanks for the insight, it was nice to know I was on the right path!

 

[MarkK]

What is the RecordSource of your report? What fields are in it? You have to filter on fields that are in the recordsource of the report, not the second column of a combobox. The data in a combo box, or what you called "a lookup" is not present in the report directly. You have to copy that data out of the control into a local textbox on the report. . . .

txtHRTech =[HR Tech].[Column](1)

. . . and then it shows on the report. But that data, the name of the tech, is not present in the report, not in such a way that the report's filter can use it.

Maybe your calling code knows the ID of the tech. What is the BoundColumn of the [HR Tech] control? Try. . .

DoCmd.OpenReport "YourReport", acViewPreview, , "[HR Tech] = " & TechID

See what I'm getting at there? What is the tech's ID? Is that in the report? If it is, and it is known to your calling code . . .

 

[namliam]

Neil, who is talking about tables?

What working code do you have for other fields?

Can you post (part) of your database (with mocked up data)?

 

[Neil_in_Japan]

What is the RecordSource of your report? What fields are in it? You have to filter on fields that are in the recordsource of the report, not the second column of a combobox. The data in a combo box, or what you called "a lookup" is not present in the report directly. You have to copy that data out of the control into a local textbox on the report. . . .

txtHRTech =[HR Tech].[Column](1)

. . . and then it shows on the report. But that data, the name of the tech, is not present in the report, not in such a way that the report's filter can use it.

Maybe your calling code knows the ID of the tech. What is the BoundColumn of the [HR Tech] control? Try. . .

DoCmd.OpenReport "YourReport", acViewPreview, , "[HR Tech] = " & TechID

See what I'm getting at there? What is the tech's ID? Is that in the report? If it is, and it is known to your calling code . . .

The report 'Record Source' is a table. That table has many fields. One field is called HR Tech.

HR Tech is populated via a combo box. The combo box has 3 columns (0,1,2)

The report displays the HR Tech with column widths 0";1";0" So only displaying the full name, not the email address of 2.

I have two text box that displays the text of the HR Combo box:

 txtHRTech=[HR Tech].[Column](1)
  
 and just 'cause I can
  
 txtHRTechDisplay = [txtHRTech]

For testing, I have two buttons. One is a macro button:

 Where condition = [txtHRTechDisplay]= "Neil"

I have tried iterations of quotes btw.

The second button is a VB script:

 Dim tmpHRTech As Variant
     tmpHRTech = "Neil"
     DoCmd.ApplyFilter , "[txtHRTech] = """ & tmpHRTech & """"
    Me.FilterOn = True

With each button, I am prompted via a popup box for a value of the txtHRTech field (or txtHRTechDisplay field - it doesn't matter)


I have also tried to pass the record number instead of text: (Column 0)

 Dim tmpHRTech As Variant
     tmpHRTech = "407"
     DoCmd.ApplyFilter , "[txtHRTech] = """ & tmpHRTech & """"
    Me.FilterOn = True

With the same result.

Here is the frustrating part.

I have several existing macro buttons that filter just fine on a field:

  Where condition = [Division]= "1"

Yup, I am passing a value instead of text, but I tried a value earlier too with the other button.

Lastly, I tried passing a value to HR Tech directly and skipping the txt... fields:

 Dim tmpHRTech As Variant
     tmpHRTech = "407"
     DoCmd.ApplyFilter , "[HR_Tech] = """ & tmpHRTech & """"
    Me.FilterOn = True

This code displays an error message:

Run-time Error ' 3464'

Data type mismatch in criteria expression.




There are 10 people whom use the database. I am willing to create and must create, buttons for each of them. Yes, horrible, I know, but we don't have sharepoint server to pull off their names and we don't use the windows' environment names either. The Access DB on a file server. It works great except for these buttons. Any ideas?

I would love to post the DB, let me work on it.. there is lots of first name last name data.

 

[Neil_in_Japan]

***** Solved*****

First - thanks for all of your help!


Very confusing - but I have it solved:

 Dim tmpHRTech As Variant
     tmpHRTech = "407"
     DoCmd.ApplyFilter , "[HR Tech] = " & tmpHRTech & ""
    Me.FilterOn = True

In the previous posts, I had too many quotes within the VB script. I just started deleting and running, deleting and running until it worked.

I still cannot get the button macro to work, but the script is easy and I will use that instead.

Thanks for your help!

 

[namliam]

He tech is a nummer field probably even autonumber, no quotes are needed for nummer fields, only text fields

 

[Neil_in_Japan]

He tech is a nummer field probably even autonumber, no quotes are needed for nummer fields, only text fields

Actually, HR tech is not a number field, but a lookup field to another table. The field HR Tech contains ID/Full name/Email Address in columns 0,1,2 , respectively.

Which only added to the confusion. However, I don't have to fiddle with concatenation either, so trade off there.

 

[namliam]

And your id field is what field type ???

 

[Neil_in_Japan]

And your id field is what field type ???

I set the successful filter against a combo box. Works fine.