Creating a Selective Query with a list box?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sapper - 18 Nov 2005 18:37 GMT

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.

Reply to this Message

Tom Ellison - 18 Nov 2005 21:45 GMT

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
[quoted text clipped - 13 lines]
> table being queried.
> TIA, Colin.

Reply to this Message

Sapper - 19 Nov 2005 13:52 GMT

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:
>
[quoted text clipped - 27 lines]
>> the table being queried.
>> TIA, Colin.

Reply to this Message

Tom Ellison - 20 Nov 2005 04:02 GMT

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,
>
[quoted text clipped - 43 lines]
>>> the table being queried.
>>> TIA, Colin.

Reply to this Message

Sapper - 20 Nov 2005 20:23 GMT

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:
>
[quoted text clipped - 65 lines]
>>>> the table being queried.
>>>> TIA, Colin.

Reply to this Message

Tom Ellison - 20 Nov 2005 20:29 GMT

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,
>
[quoted text clipped - 77 lines]
>>>>> the table being queried.
>>>>> TIA, Colin.

Reply to this Message

Sapper - 21 Nov 2005 22:37 GMT

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:
>
[quoted text clipped - 85 lines]
>>>>>> in the table being queried.
>>>>>> TIA, Colin.

Reply to this Message