Pass active x calendar value to query

[vavs]

I have a form that contains two controls. I have an Active X control : Calendar 12.0 which displays a calendar. I also have a button that launches a macro to do a print preview of a report. The report is based on a query that uses the date (called value in the active x control) to limit the results. I want to use two other controls to limit a different query that exists on the same report. Query 2 needs the month and year values off the control. Is there a way to get this information?

 

[vavs]

Okay, I can get the date from the calendar into a text box and from the text box into two different queries. Now what I need to do is to put information from both queries ( and possibly one other table) onto this report. The control source for the report is query 1.

What I really need to do is filter some of the data on the date and the other on the month of the year.

 

[xenou]

Typically either the query has a where clause tied to the control on the form, or the report has a filter clause tied to the report on the control:

Query:
WHERE Table.Field = [Forms]![MyFormName]![myCalendarControlName]

Or Report Filter (as a where criteria when you open the report):
Table.Field = [Forms]![MyFormName]![myCalendarControlName]

Of course with dates you may not use equals (as in my examples) but between or greater than/less than and so on and so forth to get the right date range.

 

[boblarson]

Just a suggestion but I would avoid using that ActiveX calendar control and move to a non-ActiveX solution. One big reason is that calendar control has been deprecated in Access 2010 so if you ever upgrade, you will have to find an alternative anyway. So perhaps it is better to do it now so you won't waste a bunch of time making it work all nice and then it breaking if an upgrade occurs.

 

[SydneyGeek]

Yep. I try to avoid ActiveX controls whenever I can.
I use a calendar provided by Allen Browne: http://www.allenbrowne.com/ser-51.html
It's a standard form, and works in any version from 2000 up. For 2007 / 2010 I just use the built-in popup.

Denis