Can I filter or set criteria in Reports?

[pbaldy]

The code I posted worked correctly in the sample db. Did you have the report in Report view?

 

[undefeatedskillz26]

The code I posted worked correctly in the sample db. Did you have the report in Report view?

Yes I closed the report and repopened it.

 

[pbaldy]

Works fine for me. I didn't ask if you closed it, I asked if it was open in Report view (as opposed to Preview). If I open your report in Report view and click inside the 16 Weeks textbox, the 2 records with that value disappear.

 

[undefeatedskillz26]

Works fine for me. I didn't ask if you closed it, I asked if it was open in Report view (as opposed to Preview). If I open your report in Report view and click inside the 16 Weeks textbox, the 2 records with that value disappear.

Yes I opened in report view and still I see all records. How is it that you don't see it and I see it?

Did you open the attached file where I changed the code? Wondering why it would be different. hmmm :banghead:

 

[pbaldy]

The way you have it set up, they show up until you click in that field. That's what your code does. If you don't want to see them from the start, use the code in the form to open it without them. Alternatively, you can put that code in the form's open or load event.

 

[undefeatedskillz26]

The way you have it set up, they show up until you click in that field. That's what your code does. If you don't want to see them from the start, use the code in the form to open it without them. Alternatively, you can put that code in the form's open or load event.

Okay now I understand.

I put the code in "On click" which is the wrong spot.

Now:

I put the code in "On Load" for report right now and it is working.

I will do this untill a form is created and use the botton to put the code in.

Thank you kind sir!!!!!

 

[pbaldy]

No problem.

 

[sk84gtspd]

Hey yall this looks like something I'm trying to do to but it is with a union query and a report. The "where" clause is the same criteria for both select statements in the union. How would I send that in the as part of the Open Report command? Or how else should I handle it?

 

[pbaldy]

Have you tried? It should apply to the results of the query, shouldn't matter that it's a union query.

 

[sk84gtspd]

When I click ReportBtn Access still wants me to input a value for the parameter 't_asset_master.ASSIGNED_TO' even though there is a value in Me.ReportPersonnelCombo

The code for the button click is this

 Private Sub ReportBtn_Click()
 'MsgBox ("This feature is not complete yet sorry")
 Dim rptStr As String
rptStr = "t_asset_master.ASSIGNED_TO = '" & Me.ReportPersonnelCombo & "'"
   If Len(ReportPersonnelCombo.Value & vbNullString) > 0 Then
    DoCmd.OpenReport "PersonalAssetReport", acViewPreview, , rptStr, acWindowNormal
  Else
   MsgBox ("Please select the name from the list box of the person you want to generate the report for")
  End If
  
End Sub

and the query that the report runs is this

 SELECT "" AS[LAST NAME], "" AS [FIRST NAME], t_asset_master.ASSET_MASTER_PHY_ID, valid_type.CODE AS TYPE, t_asset_master.BARCODE, valid_manuf.CODE AS MANUFACTURER, t_asset_master.MODEL
FROM ((t_asset_master INNER JOIN t_asset_personnel ON t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID) INNER JOIN t_asset_valid AS valid_type ON t_asset_master.TYPE = valid_type.VALID_PHY_ID) INNER JOIN t_asset_valid AS valid_manuf ON t_asset_master.MANUFACTURER = valid_manuf.VALID_PHY_ID
 UNION SELECT DISTINCT t_asset_personnel.LAST_NAME,  t_asset_personnel.FIRST_NAME, NULL, NULL, NULL, NULL, NULL
FROM (t_asset_master INNER JOIN t_asset_personnel ON t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID);

The idea is that I get the first and last name of the people and a list of the items assigned to them

To clarify, I want the query to look like this except I can change the value of ASSIGNED_TO

 SELECT "" AS[LAST NAME], "" AS [FIRST NAME], t_asset_master.ASSET_MASTER_PHY_ID, valid_type.CODE AS TYPE, t_asset_master.BARCODE, valid_manuf.CODE AS MANUFACTURER, t_asset_master.MODEL
FROM ((t_asset_master INNER JOIN t_asset_personnel ON t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID) INNER JOIN t_asset_valid AS valid_type ON t_asset_master.TYPE = valid_type.VALID_PHY_ID) INNER JOIN t_asset_valid AS valid_manuf ON t_asset_master.MANUFACTURER = valid_manuf.VALID_PHY_ID
WHERE t_asset_master.ASSIGNED_TO = '5365'
 UNION SELECT DISTINCT t_asset_personnel.LAST_NAME,  t_asset_personnel.FIRST_NAME, NULL, NULL, NULL, NULL, NULL
FROM (t_asset_master INNER JOIN t_asset_personnel ON t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID)
WHERE t_asset_master.ASSIGNED_TO = '5365'

 

[pbaldy]

ASSIGNED_TO is not a field returned by the query.

 

[sk84gtspd]

Forgive me, but I don't understand why that is important.

The query where the parameter is defined works.

 

[pbaldy]

When you use the wherecondition argument of OpenReport, you're saying "open the report but only include records where the assigned to field equals blah". In your case, there is no assigned to field returned, so you get a parameter prompt. You can add the field to both portions of the union query (the SELECT clause), or change your second SQL to use the combo instead of a hard-coded value.

 

[sk84gtspd]

Ok so my report is 48 pages when it should only have about 5 entries
I'm not sure why but here is the code I implemented.

 Private Sub reportBtn_Click()
  
 Dim rptStr As String
  
 rptStr = "'" & Me.ReportPersonnelCombo & "'"
  
   If Len(ReportPersonnelCombo.Value & vbNullString) > 0 Then
    DoCmd.OpenReport "PersonalAssetReport", acViewPreview, , rptStr, acWindowNormal
  Else
   MsgBox ("Please select the name from the list box of the person you want to generate the report for")
  End If
   
End Sub

My query was changed to this, i.e. adding ASSIGNED_TO

 SELECT "" AS[LAST NAME], "" AS [FIRST NAME],
 t_asset_master.ASSET_MASTER_PHY_ID, valid_type.CODE AS TYPE, 
 t_asset_master.BARCODE, valid_manuf.CODE AS MANUFACTURER, 
 t_asset_master.MODEL, t_asset_master.ASSIGNED_TO

 FROM ((t_asset_master INNER JOIN t_asset_personnel ON 
 t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID) INNER 
 JOIN t_asset_valid AS valid_type ON t_asset_master.TYPE = 
 valid_type.VALID_PHY_ID) INNER JOIN t_asset_valid AS valid_manuf ON 
 t_asset_master.MANUFACTURER = valid_manuf.VALID_PHY_ID
 
UNION SELECT DISTINCT t_asset_personnel.LAST_NAME,  
 t_asset_personnel.FIRST_NAME, NULL, NULL, NULL, NULL, NULL, NULL

 FROM (t_asset_master INNER JOIN t_asset_personnel ON 
 t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID);

And the results with the hard-coded ASSIGNED_TO look like this:
See attachement.

It seems like it should work be I'm apparently missing something


Thanks for checking it out.

 

[pbaldy]

You changed the filter to exclude the field name. You want what you had before:

rptStr = "ASSIGNED_TO = '" & Me.ReportPersonnelCombo & "'"

 

[sk84gtspd]

HAHA thank you. I fixed that. I got the right data in my report, BUT now I lost the first and last name that would go into the report.

I made a change because I had NULL instead of ASSIGNED to as the last field on the second select statement. For completeness here it is. I copied it from the hard code one that produced the data for the previous snip.

SELECT "" AS[LAST NAME], "" AS [FIRST NAME], 
 t_asset_master.ASSET_MASTER_PHY_ID, valid_type.CODE AS TYPE, 
 t_asset_master.BARCODE, valid_manuf.CODE AS MANUFACTURER, 
 t_asset_master.MODEL,t_asset_master.ASSIGNED_TO

 FROM ((t_asset_master INNER JOIN t_asset_personnel ON 
 t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID) INNER 
 JOIN t_asset_valid AS valid_type ON t_asset_master.TYPE = 
 valid_type.VALID_PHY_ID) INNER JOIN t_asset_valid AS valid_manuf ON 
 t_asset_master.MANUFACTURER = valid_manuf.VALID_PHY_ID

 UNION SELECT DISTINCT t_asset_personnel.LAST_NAME, 
 t_asset_personnel.FIRST_NAME, NULL, NULL, NULL, NULL, NULL, 
 t_asset_master.ASSIGNED_TO

 FROM (t_asset_master INNER JOIN t_asset_personnel ON 
 t_asset_master.ASSIGNED_TO = t_asset_personnel.USER_PHY_ID);

 

[pbaldy]

So it's working now, or there's still a problem with names? The first clause isn't selecting any names.

 

[sk84gtspd]

The textbox that had LAST NAME as the control source and the textbox that had FIRST NAME as the control source stopped being populated. They were simply blank when the report was opened up.

I used the report wizard to make the report. The report, of course, is populated with information from the query, and the control sources updated to include ASSIGNED_TO as an option even though I don't actually need it in the report but it shows, to me at least, that the changes were processed.

So, yeah, I don't know why the NAMES stop being set, but as a quick solution I made another report using the Report Wizard and the query that was developed here. I formatted it the same as the old one and opened it using the VBA code developed here AND THAT WORKS! YAY!

So I do have it working now after remaking that report but I am curious to know why it the original report may have stopped working. Any Ideas?

P.S. Thanks for all your help it really makes my internship a lot easier.

 

[pbaldy]

I'm not clear on it, but the SQL doesn't return names for the first group of records:

SELECT "" AS[LAST NAME], "" AS [FIRST NAME]

so those records would be blank.