Trouble with filtering a report

[rasticle]

Hi,

I am having a little difficulty with my report filter. How to I make the filter form so that the user can select from different date fields for the start date and end date to pull from?? For example have the option so that the user can sort start to end date from IssueDate, InitActDave, or CRODate?? I have turned on the filter for the form. This is the query behind the form:

SELECT tblEnforcement.initActDate, tblEnforcement.croDate, tblEnforcement.issueDate, tblEnforcement.[103], tblEnforcement.[103CR], tblEnforcement.[112R1], tblEnforcement.[112R2], tblEnforcement.[304], tblEnforcement.[304CR], tblEnforcement.[312], tblEnforcement.[313], tblEnforcement.[313DQ], tblEnforcement.[313LR], tblEnforcement.[313NR], tblEnforcement.[6PC], tblEnforcement.[6PD], tblEnforcement.[6PE], tblEnforcement.[6PG], tblEnforcement.[6PL], tblEnforcement.[6PI], tblEnforcement.[6RK], tblEnforcement.[6PM], tblEnforcement.[6PN], tblEnforcement.[6PR], tblEnforcement.[6PS], tblEnforcement.[6PT], tblEnforcement.[6PV], tblEnforcement.[6PU], tblEnforcement.[6PW], tblEnforcement.[6PZ]
FROM tblEnforcement;

This is the code for the button to filter the form:

Dim myCriteria

    Select Case pick
    Case 1
        myCriteria = "IssueDate"
    Case 2
        myCriteria = "InitActDate"
    Case 3
        myCriteria = "croDate"
    End Select

    myCriteria = myCriteria & " Between #" & [Forms]![pickDateRange]![txtdatefrom] & _
             "# And #" & [Forms]![pickDateRange]![txtdateTo] & "#"

    DoCmd.openReport "CaseSummary", acViewPreview, , myCriteria

Now I am getting this error: "Syntax Error (Missing Operator) in query expression '(Between #1/1/01# AND #1/1/07#)'"

I am at a loss for what to do, I would really appreciate any help!
Thanks,
Shawn

 

[Alc]

Have you tested that myCriteria is getting set before it hits the 'between' section of code?

 

[rasticle]

How do I test that?

 

[Alc]

You could use message boxes (as below).
See if the value at each stage is what you expect to see. If not, at least you'll have narrowed down where the problem occurs.

Dim myCriteria

    Msgbox "Pick is set to " & pick

    Select Case pick
    Case 1
        myCriteria = "IssueDate"
    Case 2
        myCriteria = "InitActDate"
    Case 3
        myCriteria = "croDate"
    End Select

    Msgbox "myCriteria is set to " & myCriteria

    myCriteria = myCriteria & " Between #" & [Forms]![pickDateRange]![txtdatefrom] & _
             "# And #" & [Forms]![pickDateRange]![txtdateTo] & "#"

    Msgbox "myCriteria is set to " & myCriteria

    DoCmd.openReport "CaseSummary", acViewPreview, , myCriteria

 

[rasticle]

Ok, so in the second message box, myCriteria is set to nothing.. when it should be set to one of the list selections.

 

[rasticle]

Pick is set to: InitActDate
myCriteria is set to:
myCrieria is set to: Between #1/1/01# And #1/1/07#

 

[odin1701]

Your Select statement is wrong.

You have Case 1, 2, 3, yet the value of pick is not a number, it is a string.

Select Case pick

Case "InitActDate"

Do This

Case "Whatever Else"

etc.

 

[Alc]

So there you go.
Pick isn't 1, 2 or 3, so myCriteria isn't getting set before it hits the date part.
I assume 1, 2 and 3 are the position of the actual items the user can pick from? If so, you could try changing the case part to the words themselves, rather than using numbers.

 

[rasticle]

Awesome, works perfect now!

Thank you VERY much for all of your help!

 

[rasticle]

Okay, one last question. How do I take the user entered dates from the filter screen and put them on the report?

Thanks!

 

[rasticle]

Nevermind...

=[Forms]![PickDateRange]![txtDateFrom]