Using Filter

[Greg]

I converted my MDB file to an ADP file using SQL Server. I'm finding my
reports to no longer work using the FILTER option. I have a function that
will scan the controls on a form and build the Where Clause for the report,
which is placed into the Filter.

The following logic works for the FILTER.
ViewName![DataElementName] = Value

But, it does not work for
ViewName![DataElementName] BETWEEN Value1 AND Value2

Basically, my SQL Code Builder always returns a BETWEEN result. Any
suggestions on why this is not working. Also, I have changed the FILTER ON
value and I get an error of "One or More Operators in the filter expression
is invalid". I'm using the exact same logic that worked in my MDB database
which worked, so I'm a bit puzzled as to why this is not working.

 

[strive4peace]

Hi Greg,

instead of
expression BETWEEN Value1 AND Value2
use
expression >= Value1 and expression <= Value2


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Greg]

Here is what I ended up finding out.

It appears the MS Access Report FILTER is not 100% supported in Access Data
Projects (ADP). At least for the BETWEEN SQL Jet Sytax Script I was using.
Instead of using the FILTER property I am using the SERVERFILTER property.
Plus, instead of using Access Jet SQL Syntax I am using SQL Server Syntax.
Instead of viewName!FieldName using viewName.FieldName. This also appears to
ensure that processing is taking place at the Server, rather than the Client.

What I'm doinng now is passing in my SQL Script as an OpenArgs parameter and
then set that value to the form's SERVERFILTER value. Me.ServerFilter =
Me.OpenArgs.

Now all reports work correctly.

Hi Greg,

instead of
expression BETWEEN Value1 AND Value2
use
expression >= Value1 and expression <= Value2


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I converted my MDB file to an ADP file using SQL Server. I'm finding my
reports to no longer work using the FILTER option. I have a function that
will scan the controls on a form and build the Where Clause for the report,
which is placed into the Filter.

The following logic works for the FILTER.
ViewName![DataElementName] = Value

But, it does not work for
ViewName![DataElementName] BETWEEN Value1 AND Value2

Basically, my SQL Code Builder always returns a BETWEEN result. Any
suggestions on why this is not working. Also, I have changed the FILTER ON
value and I get an error of "One or More Operators in the filter expression
is invalid". I'm using the exact same logic that worked in my MDB database
which worked, so I'm a bit puzzled as to why this is not working.

 

[strive4peace]

Hi Greg,

thanks for sharing your solution

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Here is what I ended up finding out.

It appears the MS Access Report FILTER is not 100% supported in Access Data
Projects (ADP). At least for the BETWEEN SQL Jet Sytax Script I was using.
Instead of using the FILTER property I am using the SERVERFILTER property.
Plus, instead of using Access Jet SQL Syntax I am using SQL Server Syntax.
Instead of viewName!FieldName using viewName.FieldName. This also appears to
ensure that processing is taking place at the Server, rather than the Client.

What I'm doinng now is passing in my SQL Script as an OpenArgs parameter and
then set that value to the form's SERVERFILTER value. Me.ServerFilter =
Me.OpenArgs.

Now all reports work correctly.

Hi Greg,

instead of
expression BETWEEN Value1 AND Value2
use
expression >= Value1 and expression <= Value2


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I converted my MDB file to an ADP file using SQL Server. I'm finding my
reports to no longer work using the FILTER option. I have a function that
will scan the controls on a form and build the Where Clause for the report,
which is placed into the Filter.

The following logic works for the FILTER.
ViewName![DataElementName] = Value

But, it does not work for
ViewName![DataElementName] BETWEEN Value1 AND Value2

Basically, my SQL Code Builder always returns a BETWEEN result. Any
suggestions on why this is not working. Also, I have changed the FILTER ON
value and I get an error of "One or More Operators in the filter expression
is invalid". I'm using the exact same logic that worked in my MDB database
which worked, so I'm a bit puzzled as to why this is not working.