Filter

[andrewneal]

I have an application supply file database that will pull a single application list of multiple applicants for a certain job and certain office.

The problem is this report has to pull data from a single table, but several things are playing out in it. I created a form with the following choices for the ASF personnel to filter the report. First the PositionTitle which cooresponds to the PositionTitle on the report. Second, the Activity which cooresponds to the Activity on the report. Third, it must catch everyone who applied for the position title but does not have a preference as to which office they work in. I created, in the drop down list for Activity, an "Any" category for this. So, if the ASF personnel want to pull an applicant report for 'Animal Health Technician' in the SVRV office, then the report should only bring up these applicants and the Animal Health Techncian applicants that did not have an office preference and chose Any instead of an office.

The form ASF personnel will be using to pull this report is made up of dropdown choices for PositionTitle & Activity. The Activity2 field has the default data "Any" in it which I thought I could use to filter the personnel that fall into that category.

Here is what I have created, but it says it is too complex.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "Referral List", acViewNormal, "", "[PositionTitle]=[Forms]![ReportChoice]![PositionTitle] & [Activity]=[Forms]![ReportChoice]![Activity] & [Activity]=[Forms]![ReportChoice]![Activity2]", acPreview

 

[pbaldy]

Try this:

DoCmd.OpenReport "Referral List", acViewPreview, , "[PositionTitle]='" & [Forms]![ReportChoice]![PositionTitle] & "' AND ([Activity]='" & [Forms]![ReportChoice]![Activity] & "' OR [Activity]='" & [Forms]![ReportChoice]![Activity2] & "')"

My daughter and son-in-law are at Seymour Johnson AFB in NC.

 

[andrewneal]

Thanks! I think it is along the right path; the report now comes up, but it is blank and shows no data... Can I change it to pull from the form the PositionTitle, Activity and also the option for "Any" in the Activity box rather than using the Activity2 box?

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click

Dim stDocName As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "Referral List", acViewPreview, , "[PositionTitle]='" & [Forms]![ReportChoice]![PositionTitle] & "' AND ([Activity]='" & [Forms]![ReportChoice]![Activity] & "' OR [Activity]='" & [Forms]![ReportChoice]![Activity2] & "')"

Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click

End Sub

 

[pbaldy]

I'm not sure what you mean; you can hard code the any, if you'll always want it:

DoCmd.OpenReport "Referral List", acViewPreview, , "[PositionTitle]='" & [Forms]![ReportChoice]![PositionTitle] & "' AND ([Activity]='" & [Forms]![ReportChoice]![Activity] & "' OR [Activity]='Any')"

You're not getting any records and there are records that meet the criteria?

 

[andrewneal]

Got it working!!! Thanks Much!!!!