Access filter translation to human readible

This is why many systems like this are built with their own filtering system.
Then the selections are all made on one form and can be formatted in the way you like.

I am sure an Expert here can parse the Filter string and get you what you want,...
But if/when your filtering needs become more complex, so will this "conversion".

Again, if you use a basic "filter" Form.
All the selections are made in one place, so when done, the user will actually "See" what they selected right there on the form...
...Hence no need to "convert" it...

Normally I agree with you, but I do not have the opportunity to create what you are suggesting...This is what I have to work with.
Thanks for the tip tho.

Doing what I suggested would take about 5 minutes.
Then seeing something like this :
...would not require any "conversion"

Code on a form would be only something like this:
DoCmd.OpenReport "rptEmpSales", acViewPreview, , "EmpID=" & Me.cboEmp & " AND " & "SaleDate Between " & "#" & Me.txtStart & "#" & " AND " & "#" & Me.txtEnd & "#"

Then AFAICT, there is no need to feed the filter string to the report

So I am not sure if:
"I do not have the opportunity"
...means you don't have the time (5 minutes),  ...or there is some hard constraint that says you must do it in the way you are requesting.


...again just my opinion
I am sure another Expert will be along to help you convert the filter

;-)

Jeff

This is a peripheral comment.  I observe the following:
* The first condition (DOD between 1/1/2000 and 12/1/2000) might be a sub-optimal expression, since Month(DOD) clearly limits the date range to June.  I would think that Year(DOD)=2000 would be quicker and simpler.
* The Age column is being compared to text literals.  Unless the column is a text data type, this causes unnecessary data conversions during evaluation.  If the Age column is, in fact, a text column, you might get bad results due to string collation versus numeric collation.

==========
I've done something similar to this on one of my applications, but I constructed the descriptive text from the form state (where the user specifies the query criteria), not a filter string.  Do you have that capability or are you stuck with the filter string as your only source?

I am stuck with the filter string.
The user wants to be able to do filters that are more advanced than the form/table relationships allow.

This means they are doing filters on the "Advanced query" Screen in addition to the "filter by form element" function.

Sometimes using functions such as " Month([field])"  does not show up in a form element.  I cannot grab "Month([FIELD])" from a combo box, nor text field.

This is reason why I'm stuck with filter string (unless you know a better way).

Oh, and the form has over 40 fields to it.  (fortunatly the report only pulls six or seven entries from there).

After that, I need to translate it to readable language.
Thanks.

I created my own query builder form as an advanced feature.  It was a more extensive version of the dialog form that Jeff showed.

While there might be a way to get at the advanced filter form, I've never seen an example of it.

<The user wants to be able to do filters that are more advanced than the form/table relationships allow.>
Not really clear on what this means.
If the user's needs are this "advanced", then why can't they just use a query, then fill in the criteria there directly?
With a query you can do even more advanced filtering than is possible with the built in filtering tools.
(Multiple OR and AND, Nesting, ...etc)
Here again, the query would be the source for the report, hence no need to "pass" the filter to the report.

There have been may attempts at making the querying process more "Human readable", but they all lack the ability to be very exact in every combination of fields and criteria.
For example, what you originally asked for is simple:
"DOD is between Jan 1, 2000 and Dec 1, 2000 AND the DOD is in July, and the AGE is between 30 and 35."
...but now creating a system that will also display this in another case, would be a great deal trickier...:
"DOD is before Jan 1, 2001 AND the DOD is in not in July, AND and the AGE is greater than 45"
(Besides, if this were really "Human Readable" wouldn't you like to say what DOD actually stands for?)
;-)

Again, anything is possible, so I am not sure if anyone here will create anything much simpler than you say you already can:
"I actually know how I would do this, but I think my process would be incredibly long for such a short answer,"

...it's gonna be a long answer anyway you slice it...
;-)


Finally, I am not sure what displaying this in a message box will do?
Typically the selected criteria is printed on the report itself.
This way anyone "reading" the report would know what the criteria is.
(This to me would seem much more useful than just the person creating the filter seeing this info...)

I'm not trying to be a pain here, I just don't know if this is worth all the trouble.
;-)

It's like you are saying on one hand, that the user needs (and can create) "advanced" filtering, yet, on the other hand, ...they lack the knowledge to see the filter directly and know what it means....?
There are thousands of advanced filtering questions here and it is rare that someone needs something that is "human readable"...in the way you are requesting.

JeffCoachman

I get you Jeff, I am going down a road no developer wants to go down...I'm sure you understand the concept of "fixing someone else's work". :P  lol

What I'm trying to avoid is creating a form to open a form.
That adds steps to the users workflow.

I understand what you are saying.  I fought to duplicate the form specifically to be a "report filter form".  I was shot down.

They want to use their data entry form they enter data with to scroll around and filter the dataset from, update data as necessary, then send the form's current dataset to the report (if the filter is active, it will go with them).  if the query is easy, they user "filter by form", if the query is hard they use "advanced form filter".  

The report itself works perfectly.  I use a DoCmd.OpenReport call to open the report, call the dataset and actuate the filter.

now I just need to put the filter description into a textbox and make it readable.  the textbox on the report shows the active filter, but it looks like what the msgbox shows.  i need to translate it.

I appreciate it.  i certainly would not use this medium to create a BI solution, but here we are.  

(Note. I was using the message box as a debug tool...not an actual output.)

thanks for the wisdom so far.

Are you a consultant or contract developer?  If so, present them with a very large estimate cost of doing this.

If you are an employee, then present them with a very long time to accomplish this, relative to the project.  They need to see your time as a resource over which they have conscious control.  Let them decide how best to spend your time.

Most of the time, the client will decide that the feature isn't that necessary.

thanks...
It's not for the client...it's for who receives the report.
It's required.

who receives the report is paying for this?  Let them decide.

According to MS, we can't get to the Advanced filter form :-(
http://msdn.microsoft.com/en-us/library/office/aa211380(v=office.11).aspx

The Advanced Filter/Sort window is not available in an Access project.

I agree with aikimark 100%

So if you state you can do this already:
   "I actually know how I would do this,"
...then go with your own system.

Depending on *exactly* what you need in *every* situation, this will not be "Simple" to design by any developer's standards...

...but to be fair, you can leave this q open for a few more days, ...perhaps another expert may be wiling to take this on...

Jeff

;-)

Jeff

If the people receiving the report insist on this feature and are willing to wait on you to finish it and willing to pay the additional development cost for this feature, then we can help you.  But this is a very complicated problem.

This provided me with enough information to support my requirements.