need query limited by month

[pduncan]

I have a report that is based on a query - it is essentially a calendar based report. I would like to limit it by month. Right now the reports underlying query has (among other things) a date field, and the report reflects all dates. I would like to have a form with a combobox (cboDate) that the user selects a Month and then presses a button to open the report.

As a test I made a form called frmTest. I added a combobox (cboMonth). The rowsource is a query to tblMonth. Bound Column is 2. this is the SQL for cboMonth:
SELECT tblMonth.Month, tblMonth.Range
FROM tblMonth;


tblMonth has 3 columns (and 12 rows):

col 1 = number (# 1 to 12 depending on row) (I used this to keep the months in the correct order)
col 2 (tblMonth.Month) = Month Name (displayed in cboMonth)
col 3 (tblMonth.Range) = text that depending on month is something like this.. "between #1/1/2005# and #1/31/05#" (for row 1)

anyway - I would like to have my reports (date) query based on the text in the 3rd column of tblMonth.

Since I am not a "guru" I sometimes will place a "test" button on a form and use the msgbox to display the test text I want to work with. In this case when cmdTest is pressed, I have this code:
MsgBox Me.cboMonth.Value
and it does in fact display my desired value (example "between #1/1/2005# and #1/31/05#"

in my reports query, in the criteria for the date field I entered this:

[Forms]![test]![cboMonth]
and also tried this:
[Forms]![test]![cboMonth].[value]
but neither works-
can someone please tell me what I am doing wrong, OR if you have a better way, please let me know.



Thanks,
PDUNCAN
Memphis, TN - USA

 

[dhookom]

I would use your column to build the where clause for the DoCmd.OpenReport method.

Dim strWhere as String
strWhere = "[DateField] " & Me.cboMonth.Column(2)
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pduncan]

I am glad you replied - because my report is modeled after your calendar report.

if my reports query has this for the date :
WeekOf: DateAdd("d",-Weekday([Date]),[Date])+1

I thought by putting something in the criteria field would do the trick.
I used your example - this is my code now:

    Dim stDocName As String

    Dim strWhere As String
    strWhere = "[Date] " & Me.cboMonth.Column(2)


    stDocName = "rptCalendar3"
    DoCmd.OpenReport stDocName, acPreview, , strWhere

but it's still not working - it prompts me for [date]
(I know I have my field "date" named after a function - is this why?"

but given that this is based on your calender report, can you suggest the preferred way to limit the report by month?

Thanks,
PDUNCAN
Memphis, TN - USA

 

[PHV]

A starting point:
strWhere = "Month([Date]) = " & Me.cboMonth.Column(2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

If the first column (bound column) contains the "mont number", I think I'd try:

[tt]strWhere = "Month([Date]) = " & Me!cboMonth[/tt]

Roy-Vidar