Hi,
I've been working on an Access adp project connected to a SQL 2000 back-end and developed a filter form alongside a main form.
This form allows the users to filter on any field and in any combination they require. I've used the BuildCriteria function (which is extremely flexible) to build up the complete filter string and I apply the filter to the form with:
DoCmd.ApplyFilter , FilterStr
I realise this isn't using a ServerFilter so isn't the best, but it works no problem with the form.
I really want to then pass this same filter to a report...which is when the problems begin! I've come to realise that ServerFilters are the only way forward for reports in an ADP. (Standard .Filters are very unreliable and exhibit unpredictable results with reports!)
BUT - I cannot create the right SQL syntax for the ServerFilter when using BuildCriteria from the filter form! BuildCriteria seems to only return MDB or JET style filter strings...
eg. (Surname ALike "%SON") rather than the SQL format
Surname Like '%SON')
and (StartDate > #1/1/2004#) rather than (StartDate > '20040101')
Does anyone know of a way to get BuildCriteria to return strings in SQL (ansi-92 is it?) format? Or a way of converting the JET format correctly for all different data types (dbText, dbInteger, dbDate etc)?
Has anyone else run into this problem? I'm starting to feel lonely (and wondering why I started down this road!)
Any help or comments are very much appreciated.
Paul
I've been working on an Access adp project connected to a SQL 2000 back-end and developed a filter form alongside a main form.
This form allows the users to filter on any field and in any combination they require. I've used the BuildCriteria function (which is extremely flexible) to build up the complete filter string and I apply the filter to the form with:
DoCmd.ApplyFilter , FilterStr
I realise this isn't using a ServerFilter so isn't the best, but it works no problem with the form.
I really want to then pass this same filter to a report...which is when the problems begin! I've come to realise that ServerFilters are the only way forward for reports in an ADP. (Standard .Filters are very unreliable and exhibit unpredictable results with reports!)
BUT - I cannot create the right SQL syntax for the ServerFilter when using BuildCriteria from the filter form! BuildCriteria seems to only return MDB or JET style filter strings...
eg. (Surname ALike "%SON") rather than the SQL format
Surname Like '%SON')and (StartDate > #1/1/2004#) rather than (StartDate > '20040101')
Does anyone know of a way to get BuildCriteria to return strings in SQL (ansi-92 is it?) format? Or a way of converting the JET format correctly for all different data types (dbText, dbInteger, dbDate etc)?
Has anyone else run into this problem? I'm starting to feel lonely (and wondering why I started down this road!)
Any help or comments are very much appreciated.
Paul
