Repeating Date entry

[Guest]

I am running a series of queries that use a date range ([Beg Date] and [End
Date]).

I want to only enter the dates once instead of reentering for each query. Is
there a simple way to enter the date only once?

 

[Jeff Boyce]

If query2 uses the results of query1, you can put the same parameter
(spelled EXACTLY the same - copy/paste is best) that you use in query1 into
query2 and you won't be prompted.

"Chaining" your queries like this will allow you to enter the parameter
once.

If you are running separate queries, you'll need to take another approach...

Create a form on which you add your two controls (txtBeginDate &
txtEndDate). Then, in each of your queries that need to use those values,
refer to the form's controls to get the values, with something like:

Forms!YourFormName!txtBeginDate
and
....

NOTE: your form MUST be open, and there MUST be values in the controls for
this approach to work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gary Walter]

Hi,

PMFBI

In addition to Jeff's sage advice,
one third option is a "one-record" table

tblDateParam

field type
ID Long (pk)
BeginDate Date/Time
EndDate Date/Time

enter 1 record

1 1/1/2000 2/1/2000

Create a small form bound to
this table showing only 2 textboxes
for date fields.

To ensure "one-record" set form properties

Allow Edits Yes
Allow Deletions No
Allow Additions No
Record Selectors No
Navigation Buttons No
Dividing Lines No
Pop Up Yes
Modal Yes

And just to be sure, set recordsource
of form to

SELECT
BeginDate,
EndDate
FROM tblDateParam
WHERE ID = 1;


Add one command button that
checks whether "begindate" comes
before "enddate," then closes form.

When want to run queries, open this
form first.

Use tblDateParam in queries.

There are several ways to do this.

1) use domain lookup functions in
WHERE clause. This is "low cost"
to query since only determined once.

2) add table to query w/o joins
(Cartesian join)
then just reference fields in WHERE clause

3) use non-equi join between
original table's date field and
tblDateParam's date fields

Several of my projects use something
similar and I have had users complain
about other programs that don't remember
what dates were used previously....
something one might not initially think about
as being important.

Apologies again for butting in.

good luck,

gary

If query2 uses the results of query1, you can put the same parameter
(spelled EXACTLY the same - copy/paste is best) that you use in query1
into query2 and you won't be prompted.

"Chaining" your queries like this will allow you to enter the parameter
once.

If you are running separate queries, you'll need to take another
approach...

Create a form on which you add your two controls (txtBeginDate &
txtEndDate). Then, in each of your queries that need to use those values,
refer to the form's controls to get the values, with something like:

Forms!YourFormName!txtBeginDate
and
....

NOTE: your form MUST be open, and there MUST be values in the controls
for this approach to work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am running a series of queries that use a date range ([Beg Date] and
[End
Date]).

I want to only enter the dates once instead of reentering for each query.
Is
there a simple way to enter the date only once?