Access adp, 2007-2.0 connecting to SQL2008-2005, On a form there is a text
box with a control source of DuesPaidThru and the forms recordsource is
Members, sql is storing the date as Datetime. Typical entry is 12/01/2009.
Filter by Selection works fine on this field. When doing a server Filter by
form, if I enter a date such as 12/04/2009, Access will append quotes like
so: "12/04/2009", apply the filter and it is good. Now when attempting a
filter say greater than a date, it is not possible. Say >#12/01/2009#, just
does not work and the error returned is Cannot apply filter on one or more
fields specified in the filter property. Have you been here before, I could
use some help:}

The # date delimiter is only for Access, not SQL Server.
I didn't test it, but since "12/04/2009" works as a filter, you might try
>"12/01/2009" or >'12/01/2009', which is SQL Server's standard text and date
delimiter.

"akzeph" <(E-Mail Removed)> wrote in message
news:371C4101-EDDB-4AC0-B021-(E-Mail Removed)...
> Access adp, 2007-2.0 connecting to SQL2008-2005, On a form there is a text
> box with a control source of DuesPaidThru and the forms recordsource is
> Members, sql is storing the date as Datetime. Typical entry is 12/01/2009.
> Filter by Selection works fine on this field. When doing a server Filter
> by
> form, if I enter a date such as 12/04/2009, Access will append quotes like
> so: "12/04/2009", apply the filter and it is good. Now when attempting a
> filter say greater than a date, it is not possible. Say >#12/01/2009#,
> just
> does not work and the error returned is Cannot apply filter on one or more
> fields specified in the filter property. Have you been here before, I
> could
> use some help:}

Sorry, Paul, that does not work in this server filter applied to a form...
Access adp turns the one suggestion into
">""12/01/2009"""
The other is similarly rejected.

I just realized that if I have a field that is of type
DATE
it is possible to do a
Like '%2009-12%'
I think that is what I will do: Change the type to Date from Datetime...Then
this will show me dues paidthru in December 2009...End of another frustrating
Access issue:} Sorry Redmond <grin> I do like the app, but some of the
undocumented issues have been a challenge.

Again, with an adp: filtering on one date works, but a range will not. Does
that make sense?


"Paul Shapiro" wrote:

> The # date delimiter is only for Access, not SQL Server.
> I didn't test it, but since "12/04/2009" works as a filter, you might try
> >"12/01/2009" or >'12/01/2009', which is SQL Server's standard text and date
> delimiter.
>
> "akzeph" <(E-Mail Removed)> wrote in message
> news:371C4101-EDDB-4AC0-B021-(E-Mail Removed)...
> > Access adp, 2007-2.0 connecting to SQL2008-2005, On a form there is a text
> > box with a control source of DuesPaidThru and the forms recordsource is
> > Members, sql is storing the date as Datetime. Typical entry is 12/01/2009.
> > Filter by Selection works fine on this field. When doing a server Filter
> > by
> > form, if I enter a date such as 12/04/2009, Access will append quotes like
> > so: "12/04/2009", apply the filter and it is good. Now when attempting a
> > filter say greater than a date, it is not possible. Say >#12/01/2009#,
> > just
> > does not work and the error returned is Cannot apply filter on one or more
> > fields specified in the filter property. Have you been here before, I
> > could
> > use some help:}
>
> .
>

"akzeph" wrote:

> Sorry, Paul, that does not work in this server filter applied to a form...
> Access adp turns the one suggestion into
> ">""12/01/2009"""
> The other is similarly rejected.
>
> I just realized that if I have a field that is of type
> DATE
> it is possible to do a
> Like '%2009-12%'
> I think that is what I will do: Change the type to Date from Datetime...Then
> this will show me dues paidthru in December 2009...End of another frustrating
> Access issue:} Sorry Redmond <grin> I do like the app, but some of the
> undocumented issues have been a challenge.
>
> Again, with an adp: filtering on one date works, but a range will not. Does
> that make sense?

Update: Just found out that changing the field source to date stops the text
box from formating Month/Year. I used to have the format property for the
field mm/yyyy and it would display month year. Now fomat does not work and I
get 2009-12-4 <sigh>

>
>
> "Paul Shapiro" wrote:
>
> > The # date delimiter is only for Access, not SQL Server.
> > I didn't test it, but since "12/04/2009" works as a filter, you might try
> > >"12/01/2009" or >'12/01/2009', which is SQL Server's standard text and date
> > delimiter.
> >
> > "akzeph" <(E-Mail Removed)> wrote in message
> > news:371C4101-EDDB-4AC0-B021-(E-Mail Removed)...
> > > Access adp, 2007-2.0 connecting to SQL2008-2005, On a form there is a text
> > > box with a control source of DuesPaidThru and the forms recordsource is
> > > Members, sql is storing the date as Datetime. Typical entry is 12/01/2009.
> > > Filter by Selection works fine on this field. When doing a server Filter
> > > by
> > > form, if I enter a date such as 12/04/2009, Access will append quotes like
> > > so: "12/04/2009", apply the filter and it is good. Now when attempting a
> > > filter say greater than a date, it is not possible. Say >#12/01/2009#,
> > > just
> > > does not work and the error returned is Cannot apply filter on one or more
> > > fields specified in the filter property. Have you been here before, I
> > > could
> > > use some help:}
> >
> > .
> >