One date

[rob p]

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's processing.

I want my querys and resulting reports to use data from only one checkdate.
Query's (for my testing) work if I hard code in criteria something like
"09/23/2003". I of course get all matches with the same date. I can also put
in [date mm/dd/yyyy] in criteria line. It prompts me and I enter date then
getting same results.

What I would like to do is enter the checkdate once in a simple form and
have that date plugged into criteria line in all querys that need a date as
in my above example. A combo box would be nice but the table I look at has
multiples of the same dates.
thanks.

 

[Henry Smith]

Try a date picker control (available on your tools menu, more controls....)
I am assuming you want to do this with a form and underlying VBA code.
Create your form and insert a date picker control (dtPicker) and a command
button to execute your query. I am also assuming the field in your table is
of data type DATE.
When the user operates the date picker control and selects a date the value
of the control is the selected date (dtPicker.value = Selected date)
In the command button OnClick event place the following query.

Dim selDate as Date
selDate = Me.dtPicker.Value
"Select * From MyTable Where CheckDate = selDate"

This query will give you all the fields in all the records that match the
selected date. The value of the date picker replaces the hard coded date
you put in the query grid criteria line.

If you need more detail post a reply.
Cheers,
Henry

 

[Dirk Goldgar]

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's
processing.

I want my querys and resulting reports to use data from only one
checkdate. Query's (for my testing) work if I hard code in criteria
something like "09/23/2003". I of course get all matches with the
same date. I can also put in [date mm/dd/yyyy] in criteria line. It
prompts me and I enter date then getting same results.

What I would like to do is enter the checkdate once in a simple form
and have that date plugged into criteria line in all querys that need
a date as in my above example. A combo box would be nice but the
table I look at has multiples of the same dates.
thanks.

One approach to this would be to have the form remain open but invisible
while your database is open. Then all queries could use a reference to
the CheckDate combo box on this form as a criterion. Note that you can
set the DISTINCT option on the combo box's rowsource query, so that it
only shows one item in the list for each checkdate on file.

Another aproach, which I like better, is to have a one-row table to
store the currently selected checkdate. You'd use a form to set this
date in the table. All queries would inner-join to this table on the
checkdate field, so that they only return records for the checkdate
stored in that table. Note that this approach does involve more
modification of your queries than the previous approach.

Yet another approach is to store the specified checkdate in a
module-level variable in a standard module, with a Public function
defined that simply returns the value of that variable. Queries would
then use a call to that function in their criteria.

 

[Gary Miller]

To get the list of dates in the table without seeing dups,
create a one field query on the table using your date field.
Now go to the menu bar to View/Totals to make it a totals
query. You should now see GroupBy under your date field.
Sort it ascending and save the query as qryDateLookup and
now base your combo on this query.

Now in the criteria line of the date field in any query that
you want to use it put...

[Forms]![YourFormName]![YourComboBoxName]

.... and the query will look for the value on your form.

Gary Miller
Sisters, OR

 

[rob p]

Hi Henry. I am experimenting with all three suggestions I got. One thing, I
am on Access 2000 9.0. I cannot find the date picker control. Your code
seems to make sense but is it the version I am on? Thanks again.
rob

Try a date picker control (available on your tools menu, more controls....)
I am assuming you want to do this with a form and underlying VBA code.
Create your form and insert a date picker control (dtPicker) and a command
button to execute your query. I am also assuming the field in your table is
of data type DATE.
When the user operates the date picker control and selects a date the value
of the control is the selected date (dtPicker.value = Selected date)
In the command button OnClick event place the following query.

Dim selDate as Date
selDate = Me.dtPicker.Value
"Select * From MyTable Where CheckDate = selDate"

This query will give you all the fields in all the records that match the
selected date. The value of the date picker replaces the hard coded date
you put in the query grid criteria line.

If you need more detail post a reply.
Cheers,
Henry

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's processing.

I want my querys and resulting reports to use data from only one checkdate.
Query's (for my testing) work if I hard code in criteria something like
"09/23/2003". I of course get all matches with the same date. I can also put
in [date mm/dd/yyyy] in criteria line. It prompts me and I enter date then
getting same results.

What I would like to do is enter the checkdate once in a simple form and
have that date plugged into criteria line in all querys that need a date as
in my above example. A combo box would be nice but the table I look at has
multiples of the same dates.
thanks.

 

[rob p]

Hello. I made a one record table called tlbcheckdate. Also a query of the
same, querycheckdate. What you suggested works fine on smaller tables.

When I join this tblcheckdate with a very large table it is much slower than
to have #mm/dd/yyyy# in the criteria row.

Can I somehow replace the mm/dd/yyyy in the criteria line with something on
the order of [tblcheckdate].[checkdate]??? I am asking because this neither
[querycheckdate].[checkdate] seem to work. Thanks.

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's
processing.

I want my querys and resulting reports to use data from only one
checkdate. Query's (for my testing) work if I hard code in criteria
something like "09/23/2003". I of course get all matches with the
same date. I can also put in [date mm/dd/yyyy] in criteria line. It
prompts me and I enter date then getting same results.

What I would like to do is enter the checkdate once in a simple form
and have that date plugged into criteria line in all querys that need
a date as in my above example. A combo box would be nice but the
table I look at has multiples of the same dates.
thanks.

One approach to this would be to have the form remain open but invisible
while your database is open. Then all queries could use a reference to
the CheckDate combo box on this form as a criterion. Note that you can
set the DISTINCT option on the combo box's rowsource query, so that it
only shows one item in the list for each checkdate on file.

Another aproach, which I like better, is to have a one-row table to
store the currently selected checkdate. You'd use a form to set this
date in the table. All queries would inner-join to this table on the
checkdate field, so that they only return records for the checkdate
stored in that table. Note that this approach does involve more
modification of your queries than the previous approach.

Yet another approach is to store the specified checkdate in a
module-level variable in a standard module, with a Public function
defined that simply returns the value of that variable. Queries would
then use a call to that function in their criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gary Miller]

Rob,

If you follow the suggestion to enter the date into a form
that then you make hidden, you can use that for your
criteria...

Forms!HiddenFormName!DateFieldName

Gary Miller
Sisters, OR

Hello. I made a one record table called tlbcheckdate. Also a query of the
same, querycheckdate. What you suggested works fine on smaller tables.

When I join this tblcheckdate with a very large table it is much slower than
to have #mm/dd/yyyy# in the criteria row.

Can I somehow replace the mm/dd/yyyy in the criteria line with something on
the order of [tblcheckdate].[checkdate]??? I am asking because this neither
[querycheckdate].[checkdate] seem to work. Thanks.

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's
processing.

I want my querys and resulting reports to use data from only one
checkdate. Query's (for my testing) work if I hard code in criteria
something like "09/23/2003". I of course get all matches with the
same date. I can also put in [date mm/dd/yyyy] in criteria line. It
prompts me and I enter date then getting same results.

What I would like to do is enter the checkdate once in a simple form
and have that date plugged into criteria line in all querys that need
a date as in my above example. A combo box would be nice but the
table I look at has multiples of the same dates.
thanks.

One approach to this would be to have the form remain open but invisible
while your database is open. Then all queries could use a reference to
the CheckDate combo box on this form as a criterion. Note that you can
set the DISTINCT option on the combo box's rowsource query, so that it
only shows one item in the list for each checkdate on file.

Another aproach, which I like better, is to have a one-row table to
store the currently selected checkdate. You'd use a form to set this
date in the table. All queries would inner-join to this table on the
checkdate field, so that they only return records for the checkdate
stored in that table. Note that this approach does involve more
modification of your queries than the previous approach.

Yet another approach is to store the specified checkdate in a
module-level variable in a standard module, with a Public function
defined that simply returns the value of that variable. Queries would
then use a call to that function in their criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hello. I made a one record table called tlbcheckdate. Also a query of
the same, querycheckdate. What you suggested works fine on smaller
tables.

When I join this tblcheckdate with a very large table it is much
slower than to have #mm/dd/yyyy# in the criteria row.

Can I somehow replace the mm/dd/yyyy in the criteria line with
something on the order of [tblcheckdate].[checkdate]??? I am asking
because this neither [querycheckdate].[checkdate] seem to work.
Thanks.

Do you have the checkdate field indexed in both tables? For best
results, it should be.

 

[rob p]

I tried and it pops up a window for me to enter parameter instead of picking
up the date. The form is frmcheckdatequery with a textbox called checkdate.
It is bound to querytblcheckdate, having one field called checkdate. This is
based on tblcheckdate with again only one field, checkdate. The query I am
trying to put it in is called querymaketable with a checkdate field.

This querymaketable would have all dates included so by putting
forms!frmcheckdatequery!checkdate in criteria line should limit it to the
one date. I just can't see what I am doing wrong. Thanks. If this is
confusing, someone direct me to a similiar sample. Looking at it should
help.

Rob,

If you follow the suggestion to enter the date into a form
that then you make hidden, you can use that for your
criteria...

Forms!HiddenFormName!DateFieldName

Gary Miller
Sisters, OR

Hello. I made a one record table called tlbcheckdate. Also a query of the
same, querycheckdate. What you suggested works fine on smaller tables.

When I join this tblcheckdate with a very large table it is much slower than
to have #mm/dd/yyyy# in the criteria row.

Can I somehow replace the mm/dd/yyyy in the criteria line with something on
the order of [tblcheckdate].[checkdate]??? I am asking because this neither
[querycheckdate].[checkdate] seem to work. Thanks.

I have tables and querys based on tables that all have a field called
checkdate. One example would be "09/23/2003" (in that format).

Tables have checkdate field with all the dates from this year's
processing.

I want my querys and resulting reports to use data from only one
checkdate. Query's (for my testing) work if I hard code in criteria
something like "09/23/2003". I of course get all matches with the
same date. I can also put in [date mm/dd/yyyy] in criteria line. It
prompts me and I enter date then getting same results.

What I would like to do is enter the checkdate once in a simple form
and have that date plugged into criteria line in all querys that need
a date as in my above example. A combo box would be nice but the
table I look at has multiples of the same dates.
thanks.

One approach to this would be to have the form remain open but invisible
while your database is open. Then all queries could use a reference to
the CheckDate combo box on this form as a criterion. Note that you can
set the DISTINCT option on the combo box's rowsource query, so that it
only shows one item in the list for each checkdate on file.

Another aproach, which I like better, is to have a one-row table to
store the currently selected checkdate. You'd use a form to set this
date in the table. All queries would inner-join to this table on the
checkdate field, so that they only return records for the checkdate
stored in that table. Note that this approach does involve more
modification of your queries than the previous approach.

Yet another approach is to store the specified checkdate in a
module-level variable in a standard module, with a Public function
defined that simply returns the value of that variable. Queries would
then use a call to that function in their criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Gary Miller]

Try it with brackets and the capital F if the query didn't
do that automatically for you...

[Forms]![frmcheckdatequery]![checkdate]

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

I tried and it pops up a window for me to enter parameter instead of picking
up the date. The form is frmcheckdatequery with a textbox called checkdate.
It is bound to querytblcheckdate, having one field called checkdate. This is
based on tblcheckdate with again only one field, checkdate. The query I am
trying to put it in is called querymaketable with a checkdate field.

This querymaketable would have all dates included so by putting
forms!frmcheckdatequery!checkdate in criteria line should limit it to the
one date. I just can't see what I am doing wrong. Thanks. If this is
confusing, someone direct me to a similiar sample. Looking at it should
help.

Rob,

If you follow the suggestion to enter the date into a form
that then you make hidden, you can use that for your
criteria...

Forms!HiddenFormName!DateFieldName

Gary Miller
Sisters, OR

Hello. I made a one record table called tlbcheckdate.

Also
a query of the

same, querycheckdate. What you suggested works fine on smaller tables.

When I join this tblcheckdate with a very large table

it
is much slower than

to have #mm/dd/yyyy# in the criteria row.

Can I somehow replace the mm/dd/yyyy in the criteria

line
with something on

the order of [tblcheckdate].[checkdate]??? I am asking because this neither
[querycheckdate].[checkdate] seem to work. Thanks.



I have tables and querys based on tables that all

have
a field called

checkdate. One example would be "09/23/2003" (in

that
format).

Tables have checkdate field with all the dates

from
this year's

processing.

I want my querys and resulting reports to use data from only one
checkdate. Query's (for my testing) work if I hard code in criteria
something like "09/23/2003". I of course get all matches with the
same date. I can also put in [date mm/dd/yyyy] in criteria line. It
prompts me and I enter date then getting same results.

What I would like to do is enter the checkdate

once in
a simple form

and have that date plugged into criteria line in

all
querys that need

a date as in my above example. A combo box would

be
nice but the

table I look at has multiples of the same dates.
thanks.

One approach to this would be to have the form

remain
open but invisible

while your database is open. Then all queries could

use
a reference to

the CheckDate combo box on this form as a criterion. Note that you can
set the DISTINCT option on the combo box's rowsource query, so that it
only shows one item in the list for each checkdate

on
file.

Another aproach, which I like better, is to have a one-row table to
store the currently selected checkdate. You'd use a form to set this
date in the table. All queries would inner-join to

this
table on the

checkdate field, so that they only return records

for
the checkdate

stored in that table. Note that this approach does involve more
modification of your queries than the previous approach.

Yet another approach is to store the specified

checkdate
in a

module-level variable in a standard module, with a Public function
defined that simply returns the value of that

variable.
Queries would

then use a call to that function in their criteria.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Henry Smith]

This is not a difficult problem to solve because I have accomplished this
type of query in support of reports many times in the past and very recent
past. It is a common procedure in desk top, business analysis,
applications.
OK! I am assuming you have a resource table with many records. Each record
has a [CheckDate] field. The [CheckDate] field in the table has many
similar dates. For example; for any given date (say: 03/15/03) there will
be one or more records in the table or perhaps none. Your objective is to
enable the user of your application to select a date and retrieve a report
on the data matching the selected date. My suggestion still holds. Create a
form with a datePicker control for the user to select a date. If your
Access doesn't have a datePicker control (it is an ActiveX control)
available then you need to respond to this reply so we can get you one.
Microsoft DatePicker Control is a standard control available in Access 2000
Professional. It is also available as a download from Microsoft because it
is part of CommonControl.dll. Once you have the datePicker control on your
form then a command button will enable you to use the datedPicker.vale in an
SQL statement to acquire some recordset for your report.

I have several database applications that use this very same procedure and
they all work well. If you need more details please respond.

Cheers,
Henry