use a drop-down list for a parameter query

[mgonzales]

One of my fields is a dropdown list. I'd like to query by that field and I want the user to chose from one of the pre-populated options. Is there a way to create a parameter in queries that will allow this?

 

[boblarson]

1. Your fields should not be using lookups at table level.

2. You do this on a FORM.

 

[Kryst51]

I agree with Bob abouth the lookup at table level issue,

In your query criteria you can put something like: [Choose one S,B,F] (Where S,B,F are the options in your combo box) If there are a ton of them you don't have to put, the SBF part, especially if it is some data that people are used to.

Another and probably better way to do it is to have a form that has the combo box and either a subform or list box that will filter by the selection in the combo box. The subform or list box would have it's record source as the query. In the query the combo box field would lookup up from the combo box (You can use the build function in the criteria for that field). If you just want the query to pop up you can make a button do the same thing.

 

[mgonzales]

Sorry I should clarify....
I have the drop-down in my form. I need to report on certain information in that form. So I am creating a query and basing the report off that query. Queries allows you to create parameters so you can pull certain information. When a user opens my report I want them to have an option of what data they'd like to report out. I was hoping that there is a parameter that I can place in the criteria field for that particular item in my query, that will allow users to chose from a list of items, instead of typing their item in the pop-up parameter window when they open the report.

 

[boblarson]

Sorry I should clarify....
I have the drop-down in my form. I need to report on certain information in that form. So I am creating a query and basing the report off that query. Queries allows you to create parameters so you can pull certain information. When a user opens my report I want them to have an option of what data they'd like to report out. I was hoping that there is a parameter that I can place in the criteria field for that particular item in my query, that will allow users to chose from a list of items, instead of typing their item in the pop-up parameter window when they open the report.

You do that with a form reference to a control. See this sample for an example.

 

[Kryst51]

Sorry I should clarify....
I have the drop-down in my form. I need to report on certain information in that form. So I am creating a query and basing the report off that query. Queries allows you to create parameters so you can pull certain information. When a user opens my report I want them to have an option of what data they'd like to report out. I was hoping that there is a parameter that I can place in the criteria field for that particular item in my query, that will allow users to chose from a list of items, instead of typing their item in the pop-up parameter window when they open the report.

Create a form that has all of the options on it, and make your report query read the values from the form. You would select the values, click a button and the report would open.

 

[mgonzales]

boblarson and Kryst51 you two are genius! This is exactly what I was looking for! Thanks!!!!

 

[mgonzales]

what would the code/commands be for selecting yes/no fields? I have about 8 that I would like them to be able to choose as many as needed.

You do that with a form reference to a control. See this sample for an example.

 

[mgonzales]

I have tried to replicate the form that boblarson had a sample of but I cannot get mine to work. I am using the Select by CompanyName example. I entered in all my information into the code, but when I click the Open button it gives me a popup window stating "Enter Parameter Value". This command button is just suppose to pull up the report based off the dropdown menu selection and it does not do that. What am I doing wrong?

 

[boblarson]

I have tried to replicate the form that boblarson had a sample of but I cannot get mine to work. I am using the Select by CompanyName example. I entered in all my information into the code, but when I click the Open button it gives me a popup window stating "Enter Parameter Value". This command button is just suppose to pull up the report based off the dropdown menu selection and it does not do that. What am I doing wrong?

Can you post what you currently have?

 

[mgonzales]

This is the code that I have.
Private Sub cmdOpReportingPeriod_Click()
' this opens the report in preview mode and you select the customer using the single quotes because Me.cboCompanyName
' returns text and text needs to be surrounded by quotes.
DoCmd.OpenReport "rptReporting", acViewPreview, , "[ReportPeriod]='" & Me.cboReportingPeriod & "'"
End Sub

Should I also post the DB?

 

[boblarson]

This is the code that I have.
Private Sub cmdOpReportingPeriod_Click()
' this opens the report in preview mode and you select the customer using the single quotes because Me.cboCompanyName
' returns text and text needs to be surrounded by quotes.
DoCmd.OpenReport "rptReporting", acViewPreview, , "[ReportPeriod]='" & Me.cboReportingPeriod & "'"
End Sub

Should I also post the DB?

Yes, I meant post the database. But in the meantime, is ReportPeriod actually text in the table or number? If number it would be:

DoCmd.OpenReport "rptReporting", acViewPreview, , "[ReportPeriod]=" & Me.cboReportingPeriod

Also, make sure that the combo box is returning the value you think it is. Put a message box before the code like

MsgBox Me.cboReportingPeriod

to see if you are getting what you think it should be returning.

 

[mgonzales]

The ReportPeriod is a text field.
I tried to attach my DB but it is too large. How do I compress it to send?

 

[boblarson]

The ReportPeriod is a text field.
I tried to attach my DB but it is too large. How do I compress it to send?

First go do a COMPACT AND REPAIR and then right-click on the file and select SEND TO > COMPRESSED FOLDER

 

[mgonzales]

Thanks!
Here is the DB

 

[boblarson]

Thanks that helps. This is going to take me a bit as you are trying to use Reporting period but the data is not saved by reporting period. You need something to convert this to work. I may have to work on this at home tonight.

 

[mgonzales]

Oh my, is there anything that I can start to make this DB feature work? I really do appreciate all your help with this! Thanks a ton!

 

[boblarson]

Oh my, is there anything that I can start to make this DB feature work? I really do appreciate all your help with this! Thanks a ton!

Yeah, it will just take a bit to get there.

 

[mgonzales]

Thanks that helps. This is going to take me a bit as you are trying to use Reporting period but the data is not saved by reporting period. You need something to convert this to work. I may have to work on this at home tonight.

Hi Boblarson...did you have any luck with this DB?

 

[boblarson]

Sorry, totally slipped my mind. So, what is the report based on? The tblActivity start date and end date (anything fitting between those which fit into the reporting period of tblLeadership reporting)?