Calendar and Query Entry

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

Guest

I am working on an Access database that keeps track of the Attendance in the
Computer Lab I work in. Several of the reports I have written use a query to
pull attendance data from the database for certain date ranges.

I am trying to figure out how to use either the Calendar ActiveX object, or
the Monthly Calendar that I have seen in this forum to enter the Start Date
and the End Date for the Query entry.

How do I tie a form to a query entry? Any help would be appreciated. (I am
not quite a novice at this, I have done some simple - yet amazing around here
[low knowledge standards :)] - stuff with this thing. I just need to make it
simple to use for those that come after me when I leave the lab in the next
couple of months.)

Thanx in advance.
 
Add 2 instances of the chosen calendar control to your form and give each a
suitable name (on the "Other" tab of the properties sheet) such as...

ctl_start_date and ctl_end_date

You will now be able to use the user selected dates by using the following
variables...

[Forms]![YourFormName].[ctl_start_date] and
[Forms]![YourFormName].[ctl_end_date]

so now all you need to do is edit the WHERE clause of your query to use the
above variables and place a button on the form to run the query.

The query will look something like...

SELECT column1, column2, etc
FROM tables and join conditions
WHERE format(datefield,"mm/dd/yyyy") BETWEEN
format([Forms]![YourFormName].[ctl_start_date] ,"mm/dd/yyyy")
AND format([Forms]![YourFormName].[ctl_end_date],"mm/dd/yyyy")


Regards

Andy Hull
 
That worked ALMOST perfectly. The only problem I am having right now is that
if there is a range of dates, say May 8, 2007 to May 9, 2007, the report will
print out ALL the May 8th and May 9th dates in the database...from 2003 on.

I thank you for the help so far...I love it...

Any help with this other problem will be appreciated.

Andy Hull said:
Add 2 instances of the chosen calendar control to your form and give each a
suitable name (on the "Other" tab of the properties sheet) such as...

ctl_start_date and ctl_end_date

You will now be able to use the user selected dates by using the following
variables...

[Forms]![YourFormName].[ctl_start_date] and
[Forms]![YourFormName].[ctl_end_date]

so now all you need to do is edit the WHERE clause of your query to use the
above variables and place a button on the form to run the query.

The query will look something like...

SELECT column1, column2, etc
FROM tables and join conditions
WHERE format(datefield,"mm/dd/yyyy") BETWEEN
format([Forms]![YourFormName].[ctl_start_date] ,"mm/dd/yyyy")
AND format([Forms]![YourFormName].[ctl_end_date],"mm/dd/yyyy")


Regards

Andy Hull


Admiral Oliver Franklin Doom said:
I am working on an Access database that keeps track of the Attendance in the
Computer Lab I work in. Several of the reports I have written use a query to
pull attendance data from the database for certain date ranges.

I am trying to figure out how to use either the Calendar ActiveX object, or
the Monthly Calendar that I have seen in this forum to enter the Start Date
and the End Date for the Query entry.

How do I tie a form to a query entry? Any help would be appreciated. (I am
not quite a novice at this, I have done some simple - yet amazing around here
[low knowledge standards :)] - stuff with this thing. I just need to make it
simple to use for those that come after me when I leave the lab in the next
couple of months.)

Thanx in advance.
Click to expand...
Click to expand...
 
Hi

Sorry, that's my mistake...

Change all the occurrences of "mm/dd/yyyy" to "yyyy/mm/dd"

Regards

Andy Hull

Admiral O. F. Doom said:
That worked ALMOST perfectly. The only problem I am having right now is that
if there is a range of dates, say May 8, 2007 to May 9, 2007, the report will
print out ALL the May 8th and May 9th dates in the database...from 2003 on.

I thank you for the help so far...I love it...

Any help with this other problem will be appreciated.

Andy Hull said:
Add 2 instances of the chosen calendar control to your form and give each a
suitable name (on the "Other" tab of the properties sheet) such as...

ctl_start_date and ctl_end_date

You will now be able to use the user selected dates by using the following
variables...

[Forms]![YourFormName].[ctl_start_date] and
[Forms]![YourFormName].[ctl_end_date]

so now all you need to do is edit the WHERE clause of your query to use the
above variables and place a button on the form to run the query.

The query will look something like...

SELECT column1, column2, etc
FROM tables and join conditions
WHERE format(datefield,"mm/dd/yyyy") BETWEEN
format([Forms]![YourFormName].[ctl_start_date] ,"mm/dd/yyyy")
AND format([Forms]![YourFormName].[ctl_end_date],"mm/dd/yyyy")


Regards

Andy Hull


Admiral Oliver Franklin Doom said:
I am working on an Access database that keeps track of the Attendance in the
Computer Lab I work in. Several of the reports I have written use a query to
pull attendance data from the database for certain date ranges.

I am trying to figure out how to use either the Calendar ActiveX object, or
the Monthly Calendar that I have seen in this forum to enter the Start Date
and the End Date for the Query entry.

How do I tie a form to a query entry? Any help would be appreciated. (I am
not quite a novice at this, I have done some simple - yet amazing around here
[low knowledge standards :)] - stuff with this thing. I just need to make it
simple to use for those that come after me when I leave the lab in the next
couple of months.)

Thanx in advance.
Click to expand...
Click to expand...
Click to expand...
 
Worked like a charm...thanks for all your help, Andy.

Richard

Andy Hull said:
Hi

Sorry, that's my mistake...

Change all the occurrences of "mm/dd/yyyy" to "yyyy/mm/dd"

Regards

Andy Hull

Admiral O. F. Doom said:
That worked ALMOST perfectly. The only problem I am having right now is that
if there is a range of dates, say May 8, 2007 to May 9, 2007, the report will
print out ALL the May 8th and May 9th dates in the database...from 2003 on.

I thank you for the help so far...I love it...

Any help with this other problem will be appreciated.

Andy Hull said:
Add 2 instances of the chosen calendar control to your form and give each a
suitable name (on the "Other" tab of the properties sheet) such as...

ctl_start_date and ctl_end_date

You will now be able to use the user selected dates by using the following
variables...

[Forms]![YourFormName].[ctl_start_date] and
[Forms]![YourFormName].[ctl_end_date]

so now all you need to do is edit the WHERE clause of your query to use the
above variables and place a button on the form to run the query.

The query will look something like...

SELECT column1, column2, etc
FROM tables and join conditions
WHERE format(datefield,"mm/dd/yyyy") BETWEEN
format([Forms]![YourFormName].[ctl_start_date] ,"mm/dd/yyyy")
AND format([Forms]![YourFormName].[ctl_end_date],"mm/dd/yyyy")


Regards

Andy Hull


:

I am working on an Access database that keeps track of the Attendance in the
Computer Lab I work in. Several of the reports I have written use a query to
pull attendance data from the database for certain date ranges.

I am trying to figure out how to use either the Calendar ActiveX object, or
the Monthly Calendar that I have seen in this forum to enter the Start Date
and the End Date for the Query entry.

How do I tie a form to a query entry? Any help would be appreciated. (I am
not quite a novice at this, I have done some simple - yet amazing around here
[low knowledge standards :)] - stuff with this thing. I just need to make it
simple to use for those that come after me when I leave the lab in the next
couple of months.)

Thanx in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top