Creating a Selective Query with a list box?

[Sapper]

TIA,
I have a report based on a query, it works very well, but everytime the user
wants the report they have to type in the date in a specific format. I
thought it would be easy, but I'm having a dumb day, can't think around this
one.
Without jumping thru too many hoops (no VBA) how can I make the report have
a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field in the
table being queried.
TIA, Colin.

 

[Tom Ellison]

Dear Colin:

You could place a combo box on the form and reference that control in the
query. If the form is named FormName and the combo box is named DateCombo
and the column in the table containing the date is MyDate, then the criteria
would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single value,
the combo box has some superior capabilities.

Tom Ellison

 

[Sapper]

Dear Tom,

I do not run the report from a Form, I open the Report section and
double-click on the report name, up comes the box asking for the date to use
for the report, I type in the date (mm/dd/yyy Format) and the report is
done.

The report is based on the Query that has as a Criteria: [Select the S/D
Date: mm/dd/yyyy].

From your reply I imagine that I use a Form to run my reports from, one with
a combobox on to select the date, using your crietria statement, is this
correct?

TIA, Colin

Dear Colin:

You could place a combo box on the form and reference that control in the
query. If the form is named FormName and the combo box is named DateCombo
and the column in the table containing the date is MyDate, then the
criteria would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single
value, the combo box has some superior capabilities.

Tom Ellison

TIA,
I have a report based on a query, it works very well, but everytime the
user wants the report they have to type in the date in a specific format.
I thought it would be easy, but I'm having a dumb day, can't think around
this one.
Without jumping thru too many hoops (no VBA) how can I make the report
have a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field in
the table being queried.
TIA, Colin.

 

[Tom Ellison]

Dear Colin:

Yes, using a form to specify what you want the report to do is generally
much more powerful. I strongly recommend this.

On this form there would probably be a command button to run the report. I
recommend you do one of two things:

- disable the command button if the required selection to run the report
are not available (in the case, if no date has been selected in the combo
box)

- or, when the command button is depressed, check to see that a date has
been selected in the combo box. If it is not, display a message to the user
that the date must be selected before running the report, and do not run the
report. When the user closes the dialog with that warning, place the focus
on the combo box.

This is much better programming.

Tom Ellison

Dear Tom,

I do not run the report from a Form, I open the Report section and
double-click on the report name, up comes the box asking for the date to
use for the report, I type in the date (mm/dd/yyy Format) and the report
is done.

The report is based on the Query that has as a Criteria: [Select the S/D
Date: mm/dd/yyyy].

From your reply I imagine that I use a Form to run my reports from, one
with a combobox on to select the date, using your crietria statement, is
this correct?

TIA, Colin

Dear Colin:

You could place a combo box on the form and reference that control in the
query. If the form is named FormName and the combo box is named
DateCombo and the column in the table containing the date is MyDate, then
the criteria would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single
value, the combo box has some superior capabilities.

Tom Ellison

TIA,
I have a report based on a query, it works very well, but everytime the
user wants the report they have to type in the date in a specific
format. I thought it would be easy, but I'm having a dumb day, can't
think around this one.
Without jumping thru too many hoops (no VBA) how can I make the report
have a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field in
the table being queried.
TIA, Colin.

 

[Sapper]

Dear Tom,

Did that, works great.

I made the combo box reference to the same table as the query and get the
listing of dates, but (you knew there was going to be a "but" didn't you?)
the combo box lists all dates even duplicates.

How do I get it to list all dates available once, no duplicates?

Colin.

Dear Colin:

Yes, using a form to specify what you want the report to do is generally
much more powerful. I strongly recommend this.

On this form there would probably be a command button to run the report.
I recommend you do one of two things:

- disable the command button if the required selection to run the report
are not available (in the case, if no date has been selected in the combo
box)

- or, when the command button is depressed, check to see that a date has
been selected in the combo box. If it is not, display a message to the
user that the date must be selected before running the report, and do not
run the report. When the user closes the dialog with that warning, place
the focus on the combo box.

This is much better programming.

Tom Ellison

Dear Tom,

I do not run the report from a Form, I open the Report section and
double-click on the report name, up comes the box asking for the date to
use for the report, I type in the date (mm/dd/yyy Format) and the report
is done.

The report is based on the Query that has as a Criteria: [Select the S/D
Date: mm/dd/yyyy].

From your reply I imagine that I use a Form to run my reports from, one
with a combobox on to select the date, using your crietria statement, is
this correct?

TIA, Colin

Dear Colin:

You could place a combo box on the form and reference that control in
the query. If the form is named FormName and the combo box is named
DateCombo and the column in the table containing the date is MyDate,
then the criteria would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single
value, the combo box has some superior capabilities.

Tom Ellison


TIA,
I have a report based on a query, it works very well, but everytime the
user wants the report they have to type in the date in a specific
format. I thought it would be easy, but I'm having a dumb day, can't
think around this one.
Without jumping thru too many hoops (no VBA) how can I make the report
have a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field in
the table being queried.
TIA, Colin.

 

[Tom Ellison]

Dear Sapper:

It sounds like you made the Row Source of the combo box to be the table.
Instead, make it a query, and have the query select distinct values of the
date.

Tom Ellison

Dear Tom,

Did that, works great.

I made the combo box reference to the same table as the query and get the
listing of dates, but (you knew there was going to be a "but" didn't you?)
the combo box lists all dates even duplicates.

How do I get it to list all dates available once, no duplicates?

Colin.

Dear Colin:

Yes, using a form to specify what you want the report to do is generally
much more powerful. I strongly recommend this.

On this form there would probably be a command button to run the report.
I recommend you do one of two things:

- disable the command button if the required selection to run the report
are not available (in the case, if no date has been selected in the combo
box)

- or, when the command button is depressed, check to see that a date has
been selected in the combo box. If it is not, display a message to the
user that the date must be selected before running the report, and do not
run the report. When the user closes the dialog with that warning, place
the focus on the combo box.

This is much better programming.

Tom Ellison

Dear Tom,

I do not run the report from a Form, I open the Report section and
double-click on the report name, up comes the box asking for the date to
use for the report, I type in the date (mm/dd/yyy Format) and the report
is done.

The report is based on the Query that has as a Criteria: [Select the S/D
Date: mm/dd/yyyy].

From your reply I imagine that I use a Form to run my reports from, one
with a combobox on to select the date, using your crietria statement, is
this correct?

TIA, Colin

Dear Colin:

You could place a combo box on the form and reference that control in
the query. If the form is named FormName and the combo box is named
DateCombo and the column in the table containing the date is MyDate,
then the criteria would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single
value, the combo box has some superior capabilities.

Tom Ellison


TIA,
I have a report based on a query, it works very well, but everytime
the user wants the report they have to type in the date in a specific
format. I thought it would be easy, but I'm having a dumb day, can't
think around this one.
Without jumping thru too many hoops (no VBA) how can I make the report
have a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field in
the table being queried.
TIA, Colin.

 

[Sapper]

Dear Tom,

Yes, after I got my head screwed on right it all came together, as you said
make a query as the source.

It all worked excellent, another trick of the trade I've learnt from this
group.

Thx a lot,
Colin.

Dear Sapper:

It sounds like you made the Row Source of the combo box to be the table.
Instead, make it a query, and have the query select distinct values of the
date.

Tom Ellison

Dear Tom,

Did that, works great.

I made the combo box reference to the same table as the query and get the
listing of dates, but (you knew there was going to be a "but" didn't
you?) the combo box lists all dates even duplicates.

How do I get it to list all dates available once, no duplicates?

Colin.

Dear Colin:

Yes, using a form to specify what you want the report to do is generally
much more powerful. I strongly recommend this.

On this form there would probably be a command button to run the report.
I recommend you do one of two things:

- disable the command button if the required selection to run the report
are not available (in the case, if no date has been selected in the
combo box)

- or, when the command button is depressed, check to see that a date has
been selected in the combo box. If it is not, display a message to the
user that the date must be selected before running the report, and do
not run the report. When the user closes the dialog with that warning,
place the focus on the combo box.

This is much better programming.

Tom Ellison


Dear Tom,

I do not run the report from a Form, I open the Report section and
double-click on the report name, up comes the box asking for the date
to use for the report, I type in the date (mm/dd/yyy Format) and the
report is done.

The report is based on the Query that has as a Criteria: [Select the
S/D Date: mm/dd/yyyy].

From your reply I imagine that I use a Form to run my reports from, one
with a combobox on to select the date, using your crietria statement,
is this correct?

TIA, Colin

Dear Colin:

You could place a combo box on the form and reference that control in
the query. If the form is named FormName and the combo box is named
DateCombo and the column in the table containing the date is MyDate,
then the criteria would be:

MyDate = [FormName]![FormName]![DateCombo]

You can substitute a list box if you wish, but for selecting a single
value, the combo box has some superior capabilities.

Tom Ellison


TIA,
I have a report based on a query, it works very well, but everytime
the user wants the report they have to type in the date in a specific
format. I thought it would be easy, but I'm having a dumb day, can't
think around this one.
Without jumping thru too many hoops (no VBA) how can I make the
report have a pop-up list to select the date?
Right now the query uses this criteria statement to ask for the date
required:

[Select the Date: mm/dd/yyyy]

I need the selection be limited to a specific set of dates.

I have all of the available dates in a table, they are also a field
in the table being queried.
TIA, Colin.