Report Filter... month(date) 1

[snayjay]

I'm trying to filter a report based on the month of a date. In the table the field [DATEX] is a Medium Date format and on my form the field MONTHOPT is a 2 column value type dropdown box listing the months by name and month value. (January;1;February;2;March;3...etc.,)--Bound Column: 2 for the dropdown.

Here is the code I'm using on a RUNRPTcmd_Click()

    Select Case OPTfrm.Value
        Case 1
            DoCmd.OpenReport "DailyNumbers", acViewPreview, ,_ 
            "[DATEX] = #" & DATECAL.Value & "#", acWindowNormal
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
        Case 2
            DoCmd.OpenReport "DailyNumbers", acViewPreview, ,_ 
            "Month[DATEX] = " & MONTHOPT.Value, acWindowNormal
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
        Case 3
            DoCmd.OpenReport "DailyNumbers", acViewPreview, ,_ 
            "[DATEX] = BETWEEN #" & DATECAL.Value & "# AND #" &_
            DATECAL2.Value & "#", acWindowNormal
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
    End Select

Case 1 works fine but I can't get Case 2 or 3 to work... any advice would be appreciated. I'm getting the same error for both options 2 and 3. The error is run-time error 3075 Syntax Error(missing operator) and then identifies the applicable expression.

Oh, and I've thrown in code to close out 2 other forms after the report opens because those forms are Modal and the report won't get focus. If you know of any other way to get the report to get focus without closing those forms, that would be great too. Thanks in advance,

~Snay

 

[Remou]

How about:

[tt]DoCmd.OpenReport "DailyNumbers", acViewPreview, ,_
"Month([DATEX]) = " & MONTHOPT.Value, acWindowNormal[/tt]

[tt]DoCmd.OpenReport "DailyNumbers", acViewPreview, ,_
"[DATEX] BETWEEN #" & DATECAL.Value & "# AND #" &_
DATECAL2.Value & "#", acWindowNormal[/tt]

 

[snayjay]

Remou,

Thanks for answering so quickly... Opt 3 works now, and Opt 2 doesn't give me the error... but it doesn't give me any records either. Have any clue why. I'm testing it clicking on January which means

"Month([DATEX]) = " & MONTHOPT.value,

Month([DATEX]) = 1

Now, I have records for both January of 2006 and 2007 but no records are showing up on the report. Any suggestions?

 

[snayjay]

Ok, got it working... Just added another set of parenthesis ()

(Month([DATEX])) = " & MONTHOPT.value

Thanks again for all the help!