Parm Query/Report Lookup

[rmason]

Hi,
I have a report by department that the user needs to enter in the department name to produce the report(Parm.Query). I have tried to make it somewhat easier via wildcards but if they still don't know exactly how the data is stored, they will have a hard time. Example: Labor and Delivery is stored in the table as L&D. Is there a way to use a dropdown/lookup to select the department to run generate report?? I know this is very basic but I am having a "No brain Day" Thanks in advance!!!! Rick

 

[nateobot]

One option would be to have a report form. On the form you have a drop down and a button. Dropdown list would contain the names you want the user to see, and the parameter (hidden from user). When they click on the button it loads the report and passes the parameter to it.

 

[Jibbadiah]

Create a form with a combo box.
Include the L&D field (poor naming), and description in the combo.
Use the selection from the combo as the basis for opening your report.

strDepartment = forms![FormName].[ComboName]

strWhereClause = "Department = '" & strDepartment & "'"

DoCmd.OpenReport "ReportName", acViewPreview, , strWhereClause

 

[rmason]

Thanks!!

Thanks for the feedback!! I will try the suggestions. BTW, The department name field is fed over from our core hospital system. I agree it is not the best naming convention but is is how the data is stored(??) Again, thanks for the feedback, If you have maybe a sample form to view...that would be great!! Rick

 

[Darrell]

Here's an example which takes it a bit further as well. (The field "Item Number" isn't a combo box in this example but the concept is the same.)
The fields on this form are unbound and are referred to in the query criteria as follows...

Date: Between (Nz([Forms]![Query Form - Sales Query]![Start Date],#1/01/999#)) And (Nz([Forms]![Query Form - Sales Query]![Finish Date],#31/12/9999#))

Item Number: Like (Nz([Forms]![Query Form - Sales Query]![Item Number],"*"))

Also, with the option groups, I then have the option of running different reports from the same data.