Combobox filter in a query for a report?

  • Thread starter Thread starter Sapper
  • Start date Start date
S

Sapper

Hi, Definately a newbie to Access 2000, used to dabble with 97, and I do not
use it every day, so everything has to be simple ;-}
Have a query to select a specific set of records from a table by typing in a
date (1/12/2004 etc.), how can I do this using a dropdown combobox ? The
dates involved are a limited list, but assigned to variuos records in the
table.
TIA, Colin D.
 
Colin-

You can either use a parameter query for your report or generate a
WhereCondition in the code you use to open the report. To use a parameter
that points to the combo box on your open form, the syntax is:

[Forms]![MyForm]![DateCombo]

... where "MyForm" is the name of the form containing the combo box with a
date selected and "DateCombo" is the name of the combo box.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Sapper said:
Hi, Definately a newbie to Access 2000, used to dabble with 97, and I do not
use it every day, so everything has to be simple ;-}
Have a query to select a specific set of records from a table by typing in a
date (1/12/2004 etc.), how can I do this using a dropdown combobox ? The
dates involved are a limited list, but assigned to variuos records in the
table.
Click to expand...


You can't get a query parameter prompt to use a combo box,
but a cleaner/better way is to create a form with the
desired combo box along with a button that prints the
report, opens the query, or whatever you doing with it.

You can then change the query's parameter from a prompt
string to a reference to the combo box:
Forms!nameofform.nameofcombo

Depending on what you're doing with the query, there may be
other approaches that can make the query independent of the
form.
 
O.K. I'm using a select query as content for a report, but only need to
select specific records, all records are for specific dates, what I would
like to do is make it easier for people to run the report by selecting from
a dropdown box (combobox or listbox) one of several dates rather than them
"guess" the date. I've designed the layout of the report (using the results
of the query) and done a basic query which works, but like I said the
selection date is by guess work.
= =
Colin D.
 
Create a form to manage printing the report. Add a combo
box with the date list. Now, use the command button wizard
to create a button to open the report.

You can then modify the wizard generated code behind the
button so that it filters the report. You should end up
with code that looks something like this:

Dim stDoc As String
Dim stWhere As String
stDoc = "name of report"
stWhere = "[nameofdatefield = #" & nameofcombo & "#"
DoCmd.OpenReport stDoc, acViewPreview, , stWhere

Be sure to check Help on any part of the above that you
don't understand.
 
Looks good, tried it, works good. I'm still a novice when it comes to VBA
etc., used to the old macros, so this took a little time to get thru my head
to work right.
Thx, Colin D.

Marshall Barton said:
Create a form to manage printing the report. Add a combo
box with the date list. Now, use the command button wizard
to create a button to open the report.

You can then modify the wizard generated code behind the
button so that it filters the report. You should end up
with code that looks something like this:

Dim stDoc As String
Dim stWhere As String
stDoc = "name of report"
stWhere = "[nameofdatefield = #" & nameofcombo & "#"
DoCmd.OpenReport stDoc, acViewPreview, , stWhere

Be sure to check Help on any part of the above that you
don't understand.
--
Marsh
MVP [MS Access]



O.K. I'm using a select query as content for a report, but only need to
select specific records, all records are for specific dates, what I would
like to do is make it easier for people to run the report by selecting from
a dropdown box (combobox or listbox) one of several dates rather than them
"guess" the date. I've designed the layout of the report (using the results
of the query) and done a basic query which works, but like I said the
selection date is by guess work.

do
not typing
in a
Click to expand...
Click to expand...
 
Back
Top