all dates * or between

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting between
dates once * is entered for all dates.
Thank you for any help

Kevin
 
A bit more info on what Chris is talking about.

Where is this going? Is it to filter a form? To open a report?

If it is for a report, you can use the WhereCondition of the OpenReport
action. For an example, see method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If it is to filter a form, you can do the same to create the Filter of the
form, i.e. add a couple of unbound text boxes to the form, build the
strWhere string, and then:
Me.Filter = strWhere
Me.FilterOn = True
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Chris2 said:
access2002-73 said:
I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting between
dates once * is entered for all dates.
Thank you for any help

Kevin
Click to expand...

Kevin,

When you create queries that generate automatic prompts by using []
square bracket marks around phrases, that is a sort of "automatic"
feature offered by MS Access.

If you have a query that has the following:

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Then MS Access is *going* to automatically make both requests.

There is no way I know of to "stop" MS Access from asking for the
second bit of information based on the values entered into the first
bit.

The way around this is a bit more complicated. It involves creating
your own data entry forms that can use VBA code to validate and act on
user entered data.
Click to expand...
 
access2002-73 said:
I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting between
dates once * is entered for all dates.
Thank you for any help

Kevin
Click to expand...

Kevin,

When you create queries that generate automatic prompts by using []
square bracket marks around phrases, that is a sort of "automatic"
feature offered by MS Access.

If you have a query that has the following:

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Then MS Access is *going* to automatically make both requests.

There is no way I know of to "stop" MS Access from asking for the
second bit of information based on the values entered into the first
bit.

The way around this is a bit more complicated. It involves creating
your own data entry forms that can use VBA code to validate and act on
user entered data.


Sincerely,

Chris O.
 
Yes, the query is for a report that prompts to enter * for all record dates,
and between record dates. So, users can choose between record dates
Between [Please Enter Starting Date] And [Please Enter Ending Date]
or all dates * in a report.
Now, I am going to remove Like [Enter (* for all dates) Record Date:] and
like to use Between only because parameter boxes confuse users otherwise.
If I use Between [Please Enter Starting Date] And [Please Enter Ending Date]
then How can I give users an option to pull all record dates?
Thank you for your help again
--
Kevin - Analyst


Allen Browne said:
A bit more info on what Chris is talking about.

Where is this going? Is it to filter a form? To open a report?

If it is for a report, you can use the WhereCondition of the OpenReport
action. For an example, see method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If it is to filter a form, you can do the same to create the Filter of the
form, i.e. add a couple of unbound text boxes to the form, build the
strWhere string, and then:
Me.Filter = strWhere
Me.FilterOn = True
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Chris2 said:
access2002-73 said:
I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting between
dates once * is entered for all dates.
Thank you for any help

Kevin
Click to expand...

Kevin,

When you create queries that generate automatic prompts by using []
square bracket marks around phrases, that is a sort of "automatic"
feature offered by MS Access.

If you have a query that has the following:

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Then MS Access is *going* to automatically make both requests.

There is no way I know of to "stop" MS Access from asking for the
second bit of information based on the values entered into the first
bit.

The way around this is a bit more complicated. It involves creating
your own data entry forms that can use VBA code to validate and act on
user entered data.
Click to expand...
Click to expand...
 
Ok this is only one works for me w/o excessive parameter boxes:
Between [Please Enter Starting Date] And [Please Enter Ending Date] Or
Between #09/01/2005# And #12/31/2005#
 
The suggestion is to remove the parameters from the query completely.

Instead, put a pair of text boxes on a form, with a command button to open
the report. When the button is clicked, your code builds the WhereCondition
from the non-blank boxes. It works like this:
a) You entered both dates: The report shows records between both dates.

b) You entered a FROM date only: The report shows all records from that date
onwards.

c) You entered a TO date only: The report shows all records up to and
including that date.

d) You entered neigher date: The report shows all records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

access2002-73 said:
Yes, the query is for a report that prompts to enter * for all record
dates,
and between record dates. So, users can choose between record dates
Between [Please Enter Starting Date] And [Please Enter Ending Date]
or all dates * in a report.
Now, I am going to remove Like [Enter (* for all dates) Record Date:] and
like to use Between only because parameter boxes confuse users otherwise.
If I use Between [Please Enter Starting Date] And [Please Enter Ending
Date]
then How can I give users an option to pull all record dates?
Thank you for your help again
--
Kevin - Analyst


Allen Browne said:
A bit more info on what Chris is talking about.

Where is this going? Is it to filter a form? To open a report?

If it is for a report, you can use the WhereCondition of the OpenReport
action. For an example, see method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If it is to filter a form, you can do the same to create the Filter of
the
form, i.e. add a couple of unbound text boxes to the form, build the
strWhere string, and then:
Me.Filter = strWhere
Me.FilterOn = True

message
message I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting
between
dates once * is entered for all dates.
Thank you for any help

Kevin

Kevin,

When you create queries that generate automatic prompts by using []
square bracket marks around phrases, that is a sort of "automatic"
feature offered by MS Access.

If you have a query that has the following:

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Then MS Access is *going* to automatically make both requests.

There is no way I know of to "stop" MS Access from asking for the
second bit of information based on the values entered into the first
bit.

The way around this is a bit more complicated. It involves creating
your own data entry forms that can use VBA code to validate and act on
user entered data.
Click to expand...
Click to expand...
Click to expand...
 
Thank you all -
Allen, I tried your input but it wasn't exactly what I wanted, but I
resolved the parameter issue. Many thanks for your help.
I did put your web site in my favorites, very useful, informative.
--
Kevin - Analyst


Allen Browne said:
The suggestion is to remove the parameters from the query completely.

Instead, put a pair of text boxes on a form, with a command button to open
the report. When the button is clicked, your code builds the WhereCondition
from the non-blank boxes. It works like this:
a) You entered both dates: The report shows records between both dates.

b) You entered a FROM date only: The report shows all records from that date
onwards.

c) You entered a TO date only: The report shows all records up to and
including that date.

d) You entered neigher date: The report shows all records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

access2002-73 said:
Yes, the query is for a report that prompts to enter * for all record
dates,
and between record dates. So, users can choose between record dates
Between [Please Enter Starting Date] And [Please Enter Ending Date]
or all dates * in a report.
Now, I am going to remove Like [Enter (* for all dates) Record Date:] and
like to use Between only because parameter boxes confuse users otherwise.
If I use Between [Please Enter Starting Date] And [Please Enter Ending
Date]
then How can I give users an option to pull all record dates?
Thank you for your help again
--
Kevin - Analyst


Allen Browne said:
A bit more info on what Chris is talking about.

Where is this going? Is it to filter a form? To open a report?

If it is for a report, you can use the WhereCondition of the OpenReport
action. For an example, see method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If it is to filter a form, you can do the same to create the Filter of
the
form, i.e. add a couple of unbound text boxes to the form, build the
strWhere string, and then:
Me.Filter = strWhere
Me.FilterOn = True

message

message I am trying to create a query that runs record dates as follows:
Like [Enter (* for all dates) Record Date:]
Between [Please Enter Starting Date] And [Please Enter Ending Date]
the problem I have is that I need a formula that stops prompting
between
dates once * is entered for all dates.
Thank you for any help

Kevin

Kevin,

When you create queries that generate automatic prompts by using []
square bracket marks around phrases, that is a sort of "automatic"
feature offered by MS Access.

If you have a query that has the following:

Between [Please Enter Starting Date] And [Please Enter Ending Date]

Then MS Access is *going* to automatically make both requests.

There is no way I know of to "stop" MS Access from asking for the
second bit of information based on the values entered into the first
bit.

The way around this is a bit more complicated. It involves creating
your own data entry forms that can use VBA code to validate and act on
user entered data.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top