Complex form filter 2

[rasticle]

I guess it is not that complicated, but I have a report that I am filtering by date. I have it working fine. But I want to add the option to allow the user to select by which date they filter the report. Right now I have a filter form with txtStartDate and txtEndDate and in the query (I just used Access' design view) I have this attached to the date I am filtering: >=[froms]![pickDateRange]![txtStartDate] AND <=[forms]![pickDateRange]![txtEndDate]. How would I add a drop down menu or something that would allow the user to select from different date fields?

Thanks!

 

[rasticle]

Or is there a good tutorial for setting up report filters maybe? Its giving me a huge headache!

Thanks

 

[puforee]

I don't do it in a very elegant way, but it works for me. In the underline query for the report I put in critera for Start Date. =[Enter: Start Date] and for the End Date. =[Enter: End Date].

This is very simplistic but it works.

 

[maxhugen]

FYI, you can use BETWEEN x AND y, eg:

Between [blue][forms][/blue]![pickDateRange]![txtStartDate] And [forms]![pickDateRange]![txtEndDate]

(note you had a typo, marked in blue)



Max Hugen
Australia

 

[rasticle]

I do that portion the same way, with the start date and end date. But 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 shipping date or order date??

 

[maxhugen]

Most of my programs use quite a lot of reports, most of which can be optionally be filtered by numerous criteria.

I use a Report Manager form, for this purpose. It has a list which displays the available reports. The contents of the list changes depending on an option group - this groups reports into 20-30, rather than having a list of 200-300!

When a report is selected in the list, any applicable criteria fields (textboxes, combos etc) are displayed. These usually display a default such as '[All]' etc.

When the user clicks Print or preview, VBA code strings together the criteria, and uses that string as the WHERE in the OpenReport method.

In your case, you could present the user with an option group to pick either shipping date or order date, plus the start and end date fields.

That part of the code might look something like:

Dim myCriteria$

Select case myOptionGroup
    case 1
        myCriteria = "ShippingDate"
    case 2
        myCriteria = "OrderDate"
End select

myCriteria = myCriteria & " Between #" & [forms]![pickDateRange]![txtStartDate] & _
             "# And #" & [forms]![pickDateRange]![txtEndDate]

docmd.OpenReport myReport, acViewPreview, ,myCriteria

Max Hugen
Australia

 

[rasticle]

Okay I have the code in and I changed it to fit my needs, the only problem is that I am getting a new error. =( I really wish I was better at this.

The new error reads: "Syntax Error (Missing Operator) in query expression '(Between #1/1/01# AND #1/1/07#)'"

This is the code I used:

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

I set up a combo box (pick) to have the list IssueDate, CRODate, and InitActDate. I turned on the filter option for the report. I am going to keep messing around with it to see if I can get it to work, but if anyone has any clue as to why it would be giving me this error I would be very appriciative!

 

[rasticle]

I guess I should also mention that the query behind the report is a simple one:

SELECT tblEnforcement.initActDate, tblEnforcement.croDate, tblEnforcement.issueDate
FROM tblEnforcement;

 

[rasticle]

I have resolved the problem. This issue was that, using Case 1, Case 2, and Case 3 was wrong for my situation. I needed to have the string value in the case. So I used:

   Dim myCriteria

    Select Case pick
    Case "IssueDate"
        myCriteria = "IssueDate"
    Case "InitActDate"
        myCriteria = "InitActDate"
    Case "CRODate"
        myCriteria = "CRODate"
    End Select

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

    DoCmd.openReport "CaseSummary", acViewPreview, , myCriteria

Thanks for all your help.

 

[PHV]

So, why not simply this ?
Dim myCriteria
myCriteria = pick & " Between #" & [Forms]![pickDateRange]![txtdatefrom] & _
"# And #" & [Forms]![pickDateRange]![txtdateTo] & "#"
DoCmd.openReport "CaseSummary", acViewPreview, , myCriteria

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886