Lounce's Date Ranges

[Guest]

I am a Wizard-only user...In design view on query's, I know how to query for
a date range. But I don't want end-users to select date ranges this way. I
have not been able to successfully get a parameter set up for date range. Is
there supposed to be a parameter entry PLUS an entry on the criteria line if
design view? What should the entries be for flexible, weekly date range
queries? Thanks, Lounce

 

[Jeff Conrad]

in message:

I am a Wizard-only user...In design view on query's, I know how to query for
a date range. But I don't want end-users to select date ranges this way. I
have not been able to successfully get a parameter set up for date range. Is
there supposed to be a parameter entry PLUS an entry on the criteria line if
design view? What should the entries be for flexible, weekly date range
queries? Thanks, Lounce

Hi Lounce,

The best way to gather date criteria from users is through a form. Having
users enter dates (or any other criteria) directly into a query prompt is
not very professional and you will most likely lead to user frustration on
your hands.

With a form-based prompt you can do a whole bunch of stuff even before
launching your query, macro, report, other form, etc.

To illustrate here is a simple example. Create a new blank form called
frmDateRange with two unbound text boxes. Call them txtBeginningDate
and txtEndingDate. Let's say for simplicity we just want to run a query
on some invoice dates that fall within a time frame specified by the user.
In the query we will put this on the *criteria* for the field called InvoiceDate:

Between [Forms]![frmDateRange]![txtBeginningDate] And [Forms]![frmDateRange]![txtEndingDate]

Now we have a command button that opens this query. The query will look
for the values on the form and find any invoices that fall within the date
range specified by the user.

By having the user enter the date values on the form and press a button
to "do something" we as the developer can do all of the following (and
even more if need be):
- Verify the user actually entered something into both fields
- Verify the user actually put in some *date* values into both fields
- Verify that the End date is not before the Start date
- Pop up a nice calendar on the form to aid the user in selecting dates

This is much more professional and will alleviate a lot of user confusion.

 

[John Vinson]

I am a Wizard-only user...In design view on query's, I know how to query for
a date range. But I don't want end-users to select date ranges this way. I
have not been able to successfully get a parameter set up for date range. Is
there supposed to be a parameter entry PLUS an entry on the criteria line if
design view? What should the entries be for flexible, weekly date range
queries? Thanks, Lounce

A parameter query IS a query with an entry on the criteria line in
design view. That's how you *create* a parameter query - open a query
in design view and type a parameter on the query line.

In this case, type

BETWEEN [Enter start date:] AND [Enter end date:]

unless you mean something else by "flexible, weekly date range" - do
you want to automatically query for the current week, or what?

John W. Vinson[MVP]

 

[Guest]

Thanks for responding, Jeff.
Your instructions are very nice. I would never pass a query prompt over to
end users.

Your idea worked very well!
Thanks again
Lounce

in message:

I am a Wizard-only user...In design view on query's, I know how to query for
a date range. But I don't want end-users to select date ranges this way. I
have not been able to successfully get a parameter set up for date range. Is
there supposed to be a parameter entry PLUS an entry on the criteria line if
design view? What should the entries be for flexible, weekly date range
queries? Thanks, Lounce

Hi Lounce,

The best way to gather date criteria from users is through a form. Having
users enter dates (or any other criteria) directly into a query prompt is
not very professional and you will most likely lead to user frustration on
your hands.

With a form-based prompt you can do a whole bunch of stuff even before
launching your query, macro, report, other form, etc.

To illustrate here is a simple example. Create a new blank form called
frmDateRange with two unbound text boxes. Call them txtBeginningDate
and txtEndingDate. Let's say for simplicity we just want to run a query
on some invoice dates that fall within a time frame specified by the user.
In the query we will put this on the *criteria* for the field called InvoiceDate:

Between [Forms]![frmDateRange]![txtBeginningDate] And [Forms]![frmDateRange]![txtEndingDate]

Now we have a command button that opens this query. The query will look
for the values on the form and find any invoices that fall within the date
range specified by the user.

By having the user enter the date values on the form and press a button
to "do something" we as the developer can do all of the following (and
even more if need be):
- Verify the user actually entered something into both fields
- Verify the user actually put in some *date* values into both fields
- Verify that the End date is not before the Start date
- Pop up a nice calendar on the form to aid the user in selecting dates

This is much more professional and will alleviate a lot of user confusion.

 

[Jeff Conrad]

in message:

Thanks for responding, Jeff.
Your instructions are very nice. I would never pass a query prompt over to
end users.

Your idea worked very well!
Thanks again

You're welcome Lounce, glad to help.
Good luck with your project.