Creating report with dynamic queries

[KingKen]

Hi Help needed here.

I have a table for storing logs of maintenance work done on systems in my organization.

I would like to create a report that would be able to auto generate different queries based on parameters entered by a user. for example, lets say that the user wants to know what technican X did for the week. he would open the report and have a form popup before him that allows him to enter technican X name and the date range.

in reality when he enter these names they must be placed in a query with the and/or and execuited so as to produced the desired report.

Is this possible in Access.

[puppydogbuddy]

Hi Help needed here.

I have a table for storing logs of maintenance work done on systems in my organization.

I would like to create a report that would be able to auto generate different queries based on parameters entered by a user. for example, lets say that the user wants to know what technican X did for the week. he would open the report and have a form popup before him that allows him to enter technican X name and the date range.

in reality when he enter these names they must be placed in a query with the and/or and execuited so as to produced the desired report.

Is this possible in Access.

Yes, you can create a parameter form and reference that form in the query that is the record source for your report. Page down until you come to the topic "Creating a form to supply parameters to a report" at the following link:

http://office.microsoft.com/en-us/ac...170771033.aspx

[KingKen]

Yes, you can create a parameter form and reference that form in the query that is the record source for your report. Page down until you come to the topic "Creating a form to supply parameters to a report" at the following link:

http://office.microsoft.com/en-us/ac...170771033.aspx

I followed the instructions on the site and got through to a point but it doesnt work completely. I created a form with the following fields: department, serviced by, Reported by, ProductID product model, date started and date completed. I set up this form so that it can pass this info to a querry after being called by a report. the query is as follows:

SELECT [Maintenance History].HardwareAssetID, [Maintenance History].LocationOfficer, Departments.DeptName, [Hardware Assets].[Model#], [Hardware Assets].[Serial#], [Maintenance History].DateReported, [Maintenance History].PerformedBy, [Maintenance History].ProblemDescription, [Maintenance History].Cause, [Maintenance History].SolutionDescription, [Maintenance History].DateCompleted
FROM Departments INNER JOIN ([Hardware Assets] INNER JOIN [Maintenance History] ON [Hardware Assets].HardwareAssetID = [Maintenance History].HardwareAssetID) ON Departments.DeptCode = [Hardware Assets].DepartmentCode
WHERE ((([Maintenance History].HardwareAssetID)=[Forms]![Hardware Support Loga Querry Parameters]![HardwareID])) OR ((([Maintenance History].LocationOfficer)=[Forms]![Hardware Support Loga Querry Parameters]![ReportedBy])) OR (((Departments.DeptName)=[Forms]![Hardware Support Loga Querry Parameters]![Department])) OR ((([Hardware Assets].[Model#])=[Forms]![Hardware Support Loga Querry Parameters]![Combo18])) OR ((([Hardware Assets].[Serial#])=[Forms]![Hardware Support Loga Querry Parameters]![Serial#])) OR ((([Maintenance History].DateReported)=[Forms]![Hardware Support Loga Querry Parameters]![DateReported])) OR ((([Maintenance History].PerformedBy)=[Forms]![Hardware Support Loga Querry Parameters]![ServicedBy])) OR ((([Maintenance History].DateCompleted)=[Forms]![Hardware Support Loga Querry Parameters]![DateCompleted]));
 
 

Sorry about the length... but you can disreguard the unnecessary

I want to be able to enter the product ID and be able to get the service history of that product, or if i enter the technican name then i would get all the work done by that technican. The same way if i enter the the technican name and the product ID, i must be able to acquire all the work done on that product by that technican.

What else if anything must i do to accomplish this or what have I been doing wrong?
Thanks

PS I am working in office 2003

[puppydogbuddy]

I followed the instructions on the site and got through to a point but it doesnt work completely. I created a form with the following fields: department, serviced by, Reported by, ProductID product model, date started and date completed. I set up this form so that it can pass this info to a querry after being called by a report. the query is as follows:

SELECT [Maintenance History].HardwareAssetID, [Maintenance History].LocationOfficer, Departments.DeptName, [Hardware Assets].[Model#], [Hardware Assets].[Serial#], [Maintenance History].DateReported, [Maintenance History].PerformedBy, [Maintenance History].ProblemDescription, [Maintenance History].Cause, [Maintenance History].SolutionDescription, [Maintenance History].DateCompleted
FROM Departments INNER JOIN ([Hardware Assets] INNER JOIN [Maintenance History] ON [Hardware Assets].HardwareAssetID = [Maintenance History].HardwareAssetID) ON Departments.DeptCode = [Hardware Assets].DepartmentCode
WHERE ((([Maintenance History].HardwareAssetID)=[Forms]![Hardware Support Loga Querry Parameters]![HardwareID])) OR ((([Maintenance History].LocationOfficer)=[Forms]![Hardware Support Loga Querry Parameters]![ReportedBy])) OR (((Departments.DeptName)=[Forms]![Hardware Support Loga Querry Parameters]![Department])) OR ((([Hardware Assets].[Model#])=[Forms]![Hardware Support Loga Querry Parameters]![Combo18])) OR ((([Hardware Assets].[Serial#])=[Forms]![Hardware Support Loga Querry Parameters]![Serial#])) OR ((([Maintenance History].DateReported)=[Forms]![Hardware Support Loga Querry Parameters]![DateReported])) OR ((([Maintenance History].PerformedBy)=[Forms]![Hardware Support Loga Querry Parameters]![ServicedBy])) OR ((([Maintenance History].DateCompleted)=[Forms]![Hardware Support Loga Querry Parameters]![DateCompleted]));


Sorry about the length... but you can disreguard the unnecessary

I want to be able to enter the product ID and be able to get the service history of that product, or if i enter the technican name then i would get all the work done by that technican. The same way if i enter the the technican name and the product ID, i must be able to acquire all the work done on that product by that technican.

What else if anything must i do to accomplish this or what have I been doing wrong?
Thanks

PS I am working in office 2003

There are several possibilities.
1. You have 2 criteria, with technician name an "optional" criteria that may or may not be used, so you have to handle this in the reference to the form parameters in the query grid. try entering the criteria this way in the criteria row of your query. I don't know if it will work, but I am trying to substitute a wildcard for a null if you only enter the productId. If chr(42) does not work, try using "*" instead. Replace the illustrative object names I used with their actual names.
................ProductID......................... .........TechName
Forms!YourForm!YourControl............NZ(Forms!You rForm!YourControl,Chr(42))

2. Be sure your form stays open until he query/report is finished.