Using Between in a Server Filter by Form

[Rich K]

I have an ADP with SQL Server 2000 that I am trying to employ the Server
Filter by Form. In general, the Server Filter by Form works and I can filter
records to my hearts desire. I have even allowed my users to save their
custom filters so they can grab a filter and apply it without having to
remember exactly which conditions they use. The users use the form to create
the filters. Pretty nifty so far.

The issue is with date fields. For instance, I have an admit date and a
discharge date that I would like to be able to query with between. For
example: AdmitDate Between '7/1/08' and '7/31/08' or it's equivelant
AdmitDate <='7/31/08' and AdmitDate >='7/1/08'

When I apply either of these filters, the filter is applied, but when I
retrieve the saved filter and try to reapply it to the form it does not
accept the filter as valid, and either has an empty filter or a partial
filter (AdmitDate <='7/31/08' for instance).

Any idea how I can allow for the between or the and in the server filter by
form?

 

[Sylvain Lafontaine]

Maybe some mixup with the format of the date; for example, 7/1/08 is
interpreted as 1/7/08 instead of 7/1/08. Check the format of the date on
your local machine as well as the format used with the Logon on SQL-Server.

Take a look with the SQL-Server Profiler to see exactly what's going on
here.

Personally, I never used any server filter with an ADP project. How are you
proceeding to save and restore them?

 

[Rich K]

Thanks for the reply Sylvain.

I ended up working around the issue. To save the filters I just stored them
in a table as varchar. My users use the Server Filter by Form method to
create filters. I can allow each user to have their own filters and also
have global filters that all users can access. The users name each filter so
they know what they are selecting. I then place the filter string in a
global variable which gets used when the server filter is applied. The
process of using server filters isn't well documented and I had to spend a
couple hours on the phone with Microsoft Support to get it working. If you
want me to describe the process I'll do it, but you'll have to give me a
couple of evenings to write it out so that it makes sense. (Which might be a
good exercise for me to do anyway.)

Originally I sent my users with their selected filter to the Server Filter
by Form where it was planned that they could edit the filter and continue on
to the filtered form. However, when trying to apply the filter to that form
it didn't process the saved filter properly so I changed the approach. The
issue seemed to be with the "between" operator. I have not resolved this,
and won't unless my work around is found to be inadequate.

Now I allow the users to edit the filter from the filter selection form and
then apply it to a form without any server filter by form instituted. This
seems to be an adequate solution, but it does require the user to be able to
read a bit of SQL to edit the filter. Luckily for me, the users that will be
using this have at least moderate experience in this area.

Rich

 

[Sylvain Lafontaine]

Personally, I don't feel any need for the use of server filters; however, a
lot of other people here might be interested into your code.

In your case, as I've already suggested, you should really take a look with
the SQL-Server Profiler if you want to understand what's going on under the
hood; ie., why Access is failling when it try to apply your server filters
with a BETWEEN operator in them.